Últimos mensajes - Powered by IBM

Palabras claves

copiar varias columnas de una hoja a otra mediante filtro

21/03/2007 - 21:45 por alfredoa4 | Informe spam

Hola a todos,, reciban un cordial,, saludo,, expongo mi gran problema
al cual agradecer mucho su gran ayuda
Tengo en una hoja de excel llamada MyData algo como esto:
las filas 1 a 4 tiene encabezados
A B C D E F . P Q R S T U
V W X Y Z AA AB AC AD ...
5 Bos 77 52 MB 74 52 MB 79 52
MB 75 51 MB 73 51 MB
6 Bos 77 52 MB 74 52 MB 79 52
MB 75 51 MB 73 51 MB
7 Bos 77 52 MB 74 52 MB 79 52
MB 75 51 MB 73 51 MB
8 Cir 66 44 AA 66 44 AA 66
44 AA 66 44 AA 66 44 AA
9 Cir 66 44 AA 66 44 AA 66
44 AA 66 44 AA 66 44 AA
10 Mty 99 88 MX 99 88 MX 99 88
MX 99 88 M X 99 88 MX
11 Mty 99 88 MX 99 88 MX 99 88
MX 99 88 M X 99 88 MX
12 Mty 99 88 MX 99 88 MX 99 88
MX 99 88 M X 99 88 MX

Ahora en otra hoja de excel llamada Resultados tengo un combobox con
un boton,, este boton lo que hace es actualizar los datos del combobox
basados en valores unicos de un rango de la columna F,, el codigo para
este boton es el siguiente:

Private Sub CommandButton1_Click()
Dim everyCell, singleCell As Range
Dim noDuplicates As New Collection
Dim Item

Set everyCell = Sheets("MyData").Range("F5:F201")
On Error Resume Next

For Each singleCell In everyCell
noDuplicates.Add singleCell.Value, CStr(singleCell.Value)
Next singleCell

On Error GoTo 0

ComboBox1.Clear
For Each Item In noDuplicates
ComboBox1.AddItem Item
Next Item
End Sub

Ahora aqui el problema donde pido su amable ayuda,, el valor escogido
en el combobox seria para tomar el filtro de mis datos y copiar los
datos de las columnas y pegarlas en mi hoja resultados,, ahora bien lo
que quiero hacer es que al escoger el valor primero me ponga en un
titulo en este caso "Costos" y lugo me copia los datos de la columna
p,q,r despues en donde terminar estos datos me ponga el titulo "Renta"
y me copia la columna s,t,u, luego donde terminan estos datos me ponga
otro titulo "Local" y me copia la columna v,w,x, luego nuevamente me
ponga otro titulo "Telefono" y me copia la columna y,z,aa y por ultimo
otro titulo "Otros" y me copie las columnas ab, ac, ad

El siguiente ejemplo creo puede ser mas especifico de lo que se quiere

Si yo seleccion en mi combobox el valor Bos, quisiera me devolviera
los siguiente:

a b c
7 Costs
8 77 52 MB
9 77 52 MB
10 77 52 MB
11 Rent
12 74 52 MB
13 74 52 MB
14 74 52 MB
15 Local
16 79 52 MB
17 79 52 MB
18 79 52 MB
19 Telephone
20 75 51 MB
21 75 51 MB
22 75 51 MB
23 Other
24 73 51 MB
25 73 51 MB
26 73 51 MB

Y el mismo proceso si escogiera otro valor del combobox,, alguien por
favor me pudiera ayudar???!!!

Siga el debate

12 respuestas

Tengo una respuesta

Preguntas similare

copiar celdas de varias hojas a otra hoja

Leer las respuestas

#1 Héctor Miguel

22/03/2007 - 08:20 | Informe spam

hola, alfredo !

-> puedes evitar la necesidad del combobox [como objeto incrustado] y su codigo de 'relleno' [entre otras cosas] si...

a) asegurate que en tu hoja 'mydata' tienes libre la columna 'AG' para extraer por filtros avanzados los 'unicos' de la columna 'F'

b) asigna/define/crea/... un nombre para ese rango [p.e. Listado], seleccionando primero la celda 'AG5', con [+/-] la siguiente formula:
=desref($ag$5,,,coincidir("zzzzz",$ag:$ag)-4,) <= excel completara los rangos con el nombre de la hoja ;)

c) usa [menu] datos / validacion / lista / formula y estableces como origen de la lista 'el nombre' -> =listado
[p.e. en la celda 'A2' de la hoja 'reportes'] <= es la que uso en el codigo de ejemplo
-> sustituye al combo/objeto y el evento '_change' de esa hoja realiza las acciones que solicitas :))
OJO: se requiere excel 2000 en adelante para que se dispare el evento en listas de validacion que se toman de rangos <= OJO

d) asegurate que la celda 'A6' en tu hoja de reportes NO esta 'vacia' [para que el codigo se pueda 'mover' de ahi en adelante] :))

copia/pega las siguientes lineas en el modulo de codigo de cada hoja y...
si cualquier duda [o informacion adicional]... comentas ?
saludos,
hector.

[MyData]
Private Sub Worksheet_Deactivate()
Range(Range("f4"), Range("f65536").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range("ag4"), Unique:=True
Range("ag4").Sort Key1:=Range("ag5"), Order1:=xlAscending, Header:=xlYes
End Sub

[Reportes]
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Col As Byte: Application.ScreenUpdating = False
If Target.Address <> "$A$2" Then Exit Sub Else Range("a7:c1000").ClearContents
With Worksheets("mydata"): If .AutoFilterMode Then .Cells.AutoFilter
.Range("f4").AutoFilter Field:=1, Criteria1:=Range("a2")
For Col = 10 To 22 Step 3
Range("a65536").End(xlUp).Offset(1) = _
Application.Choose(((Col - 1) / 3) - 2, "Costs", "Rent", "Local", "Phone", "Others")
With .AutoFilter.Range
With .Offset(1, Col).Resize(.Rows.Count - 1, 3)
.Copy Range("a65536").End(xlUp).Offset(1)
End With: End With: Next: .Range("f4").AutoFilter
End With: x = UsedRange.Rows.Count
End Sub

__ la consulta original __

Tengo en una hoja de excel llamada MyData algo como esto: las filas 1 a 4 tiene encabezados [...]

= [omito las 'tablas' porque no pasaron muy 'entendibles' que digamos] :-((
=> Ahora en otra hoja de excel llamada Resultados tengo un combobox con un boton

este boton lo que hace es actualizar los datos del combobox basados en valores unicos de un rango de la columna F
el codigo para este boton es el siguiente:
Private Sub CommandButton1_Click()
Dim everyCell, singleCell As Range
Dim noDuplicates As New Collection
Dim Item
Set everyCell = Sheets("MyData").Range("F5:F201")
On Error Resume Next
For Each singleCell In everyCell
noDuplicates.Add singleCell.Value, CStr(singleCell.Value)
Next singleCell
On Error GoTo 0
ComboBox1.Clear
For Each Item In noDuplicates
ComboBox1.AddItem Item
Next Item
End Sub
... el valor escogido en el combobox seria para tomar el filtro de mis datos y copiar... y pegarlas en mi hoja resultados
... quiero hacer... que al escoger el valor primero me ponga en un titulo
en este caso "Costos" y lugo me copia los datos de la columna p,q,r
despues en donde terminar estos datos me ponga el titulo "Renta" y me copia la columna s,t,u
luego donde terminan estos datos me ponga otro titulo "Local" y me copia la columna v,w,x
luego nuevamente me ponga otro titulo "Telefono" y me copia la columna y,z,aa
y por ultimo otro titulo "Otros" y me copie las columnas ab, ac, ad [...]
Y el mismo proceso si escogiera otro valor del combobox...

Responder a este mensaje

#2 alfredoa4

22/03/2007 - 16:55 | Informe spam

On 22 mar, 01:20, "Héctor Miguel"
wrote:

hola, alfredo !

-> puedes evitar la necesidad del combobox [como objeto incrustado] y su codigo de 'relleno' [entre otras cosas] si...

a) asegurate que en tu hoja 'mydata' tienes libre la columna 'AG' para extraer por filtros avanzados los 'unicos' de la columna 'F'

b) asigna/define/crea/... un nombre para ese rango [p.e. Listado], seleccionando primero la celda 'AG5', con [+/-] la siguiente formula:
=desref($ag$5,,,coincidir("zzzzz",$ag:$ag)-4,) <= excel completara los rangos con el nombre de la hoja ;)

c) usa [menu] datos / validacion / lista / formula y estableces como origen de la lista 'el nombre' -> =listado
[p.e. en la celda 'A2' de la hoja 'reportes'] <= es la que uso en el codigo de ejemplo
-> sustituye al combo/objeto y el evento '_change' de esa hoja realiza las acciones que solicitas :))
OJO: se requiere excel 2000 en adelante para que se dispare el evento en listas de validacion que se toman de rangos <= OJO

d) asegurate que la celda 'A6' en tu hoja de reportes NO esta 'vacia' [para que el codigo se pueda 'mover' de ahi en adelante] :))

copia/pega las siguientes lineas en el modulo de codigo de cada hoja y...
si cualquier duda [o informacion adicional]... comentas ?
saludos,
hector.

[MyData]
Private Sub Worksheet_Deactivate()
Range(Range("f4"), Range("f65536").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range("ag4"), Unique:=True
Range("ag4").Sort Key1:=Range("ag5"), Order1:=xlAscending, Header:=xlYes
End Sub

[Reportes]
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Col As Byte: Application.ScreenUpdating = False
If Target.Address <> "$A$2" Then Exit Sub Else Range("a7:c1000").ClearContents
With Worksheets("mydata"): If .AutoFilterMode Then .Cells.AutoFilter
.Range("f4").AutoFilter Field:=1, Criteria1:=Range("a2")
For Col = 10 To 22 Step 3
Range("a65536").End(xlUp).Offset(1) = _
Application.Choose(((Col - 1) / 3) - 2, "Costs", "Rent", "Local", "Phone", "Others")
With .AutoFilter.Range
With .Offset(1, Col).Resize(.Rows.Count - 1, 3)
.Copy Range("a65536").End(xlUp).Offset(1)
End With: End With: Next: .Range("f4").AutoFilter
End With: x = UsedRange.Rows.Count
End Sub

__ la consulta original __> Tengo en una hoja de excel llamada MyData algo como esto: las filas 1 a 4 tiene encabezados [...]

=> [omito las 'tablas' porque no pasaron muy 'entendibles' que digamos] :-((
=>

> Ahora en otra hoja de excel llamada Resultados tengo un combobox con un boton
> este boton lo que hace es actualizar los datos del combobox basados en valores unicos de un rango de la columna F
> el codigo para este boton es el siguiente:
> Private Sub CommandButton1_Click()
> Dim everyCell, singleCell As Range
> Dim noDuplicates As New Collection
> Dim Item
> Set everyCell = Sheets("MyData").Range("F5:F201")
> On Error Resume Next
> For Each singleCell In everyCell
> noDuplicates.Add singleCell.Value, CStr(singleCell.Value)
> Next singleCell
> On Error GoTo 0
> ComboBox1.Clear
> For Each Item In noDuplicates
> ComboBox1.AddItem Item
> Next Item
> End Sub
> ... el valor escogido en el combobox seria para tomar el filtro de mis datos y copiar... y pegarlas en mi hoja resultados
> ... quiero hacer... que al escoger el valor primero me ponga en un titulo
> en este caso "Costos" y lugo me copia los datos de la columna p,q,r
> despues en donde terminar estos datos me ponga el titulo "Renta" y me copia la columna s,t,u
> luego donde terminan estos datos me ponga otro titulo "Local" y me copia la columna v,w,x
> luego nuevamente me ponga otro titulo "Telefono" y me copia la columna y,z,aa
> y por ultimo otro titulo "Otros" y me copie las columnas ab, ac, ad [...]
> Y el mismo proceso si escogiera otro valor del combobox...- Ocultar texto de la cita -

- Mostrar texto de la cita -

Hola Hector muchisimas gracias pór tu ayuda,, solo tengo 2 duditas que
se refieren a las 2 primeras instrucciones,, las cuales no me quedaron
muy claras,,
la primera: necesito crear un filtro avanzado para extraer los datos
unicos de la columna F
la segunda: una vez creado este filtro pongo la formula en la celda???
puse la formula en la celda pero me marca #Name?

Me podrias por favor echar una mano con estos dos pasos??

Muchas gracias,, saludos!!

Responder a este mensaje

#3 Héctor Miguel

22/03/2007 - 18:20 | Informe spam

hola, alfredo !

a) asegurate que en tu hoja 'mydata' tienes libre la columna 'AG' para extraer por filtros avanzados los 'unicos' de la columna 'F'

b) asigna/define/crea/... un nombre para ese rango [p.e. Listado], seleccionando primero la celda 'AG5', con [+/-] la siguiente formula:
=desref($ag$5,,,coincidir("zzzzz",$ag:$ag)-4,) <= excel completara los rangos con el nombre de la hoja ;)

... solo tengo 2 duditas que se refieren a las 2 primeras instrucciones las cuales no me quedaron muy claras
la primera: necesito crear un filtro avanzado para extraer los datos unicos de la columna F
la segunda: una vez creado este filtro pongo la formula en la celda???
puse la formula en la celda pero me marca #Name?

1) los filtros avanzados los 'maneja' el codigo [tu solo asegurate que la columna 'AG' de la hoja 'mydata' esta libre] ;)

2) la creacion del nombre [p.e. Listado] es en la misma hoja 'mydata' seleccionando primero la celda 'AG5' [se llena por codigo]
a) creas el nombre 'estando parado' en la celda 'AG5' de la hoja 'mydata' con la formula propuesta...
b) vas a la hoja de reportes y en la celda 'A2' aplicas reglas de validacion por lista usando el nombre que creaste en el paso anterior

si cualquier duda... comentas ?
saludos,
hector.

Responder a este mensaje

#4 alfredoa4

22/03/2007 - 19:27 | Informe spam

On 22 mar, 11:20, "Héctor Miguel"
wrote:

hola, alfredo !

>> a) asegurate que en tu hoja 'mydata' tienes libre la columna 'AG' para extraer por filtros avanzados los 'unicos' de la columna 'F'

>> b) asigna/define/crea/... un nombre para ese rango [p.e. Listado], seleccionando primero la celda 'AG5', con [+/-] la siguiente formula:
>> =desref($ag$5,,,coincidir("zzzzz",$ag:$ag)-4,) <= excel completara los rangos con el nombre de la hoja ;)

> ... solo tengo 2 duditas que se refieren a las 2 primeras instrucciones las cuales no me quedaron muy claras
> la primera: necesito crear un filtro avanzado para extraer los datos unicos de la columna F
> la segunda: una vez creado este filtro pongo la formula en la celda???
> puse la formula en la celda pero me marca #Name?

1) los filtros avanzados los 'maneja' el codigo [tu solo asegurate que la columna 'AG' de la hoja 'mydata' esta libre] ;)

2) la creacion del nombre [p.e. Listado] es en la misma hoja 'mydata' seleccionando primero la celda 'AG5' [se llena por codigo]
a) creas el nombre 'estando parado' en la celda 'AG5' de la hoja 'mydata' con la formula propuesta...
b) vas a la hoja de reportes y en la celda 'A2' aplicas reglas de validacion por lista usando el nombre que creaste en el paso anterior

si cualquier duda... comentas ?
saludos,
hector.

Hola nuevamente,, disculpa la molestia,, pero seguramente algo estoy
haciendo mal en estos pasos porque no me funciona.. a continuacion te
describo los pasos que estoy haciendo,, si me pudieras auxiliar en que
estoy mal te lo agradeceria..

Mi excel esta en ingles

1) me pongo en la celda 'ag5' de mi hoja llamada 'Mydata'
2)para crear el nombre .. voy a mi barra de menus en la opcion
Insert / Name / Define
Aqui me aparece para poner el nombre en nuestro caso 'Listado' en la
parte de abajo de esta pantalla ahi algo que dice 'Refers to:' que es
donde pongo/copio tu formula propuesta y le doy ok.
3)Ahora voy a la hoja Reportes y me ubico en la celda 'A2'
4)Me voy al menu Data / Validation,, aqui me aparece una pantalla con
3 pestañas,, settings / Input Message / Error Alert,, yo voy a la
pestaña settings
Dice Validation criteria / Allow aqui escogo list,, Data aparaece
desabilitado y hasta abajo de esta pestaña dice source,, donde pongo
'=Listado' y le doy OK.
y aqui me aparece un error:
'The source courrently evaluates to an error. Do you wish to
continue?'

y pues ya no me hace nada,,, pense que copiando los codigos que van en
la hojas se solucionaria,, pero nada,, que estoy haciendo mal???

Saludos!!

Responder a este mensaje

#5 Héctor Miguel

23/03/2007 - 00:34 | Informe spam

hola, alfredo !

b) asigna/define/crea/... un nombre para ese rango [p.e. Listado], seleccionando primero la celda 'AG5', con [+/-] la siguiente formula:
=desref($ag$5,,,coincidir("zzzzz",$ag:$ag)-4,) <= excel completara los rangos con el nombre de la hoja ;)

... seguramente algo estoy haciendo mal en estos pasos porque no me funciona...
Mi excel esta en ingles
1) me pongo en la celda 'ag5' de mi hoja llamada 'Mydata'

2) para crear el nombre .. voy a mi barra de menus en la opcion Insert / Name / Define

Aqui me aparece para poner el nombre en nuestro caso 'Listado' en la parte de abajo de esta pantalla ahi algo que dice 'Refers to:'
que es donde pongo/copio tu formula propuesta y le doy ok. [...]

si tu excel esta en ingles... cambia la formula con la que defines el nombre:
de: -> =desref($ag$5,,,coincidir("zzzzz",$ag:$ag)-4,)
a: -> =offset($ag$5,,,match("zzzzz",$ag:$ag)-4,)

si cualquier duda [o informacion adicional]... comentas ?
saludos,
hector.

Responder a este mensaje

Ads by Google

Hacer una preguntaSiguiente

Tengo una respuesta

12 3

Busqueda sugerida