Tablas dinámicas con dos criterios
He leído muchas de tus respuestas (una parecida a la cuestión que te formulo) y poco a poco me voy iniciando en el macromundo.
Me encuentro con que precisaría construir una macro que generase una tabla dinámica de acuerdo con los siguientes requisitos:
1-Un libro excel con dos hojas. La hoja de los datos origen siempre posee el mismo nombre, pero su contenido (nº filas, nºcolumnas y fila de rótulos) es variable. En concreto, en la hoja origen pego un listado de datos y genero una nueva tabla dinámica. Borro los datos de la hoja de datos, introduzco otros y genero otra tabla, etc.
2-La tabla dinámica debe generarse en una hoja que debe mantener siempre el mismo nombre.
3-Los criterios de la tabla se reducen a dos: en filas una sola selección, y en datos una sola selección de contar nº de otros datos. Problema: poder introducir en cada ocasión el rótulo de columna para seleccionar y el rótulo para contar (como dije, la hoja origen es variable en cuanto a su contenido). P.ej. Con un InputBox.
He estado trabajando con la grabadora, buscando apuntes, etc. Pero tras bastantes horas he no logro construir la susodicha macro.
Ruego tu ayuda, dado que esta macro sería el complemento de otras varias, pero sin solucionar esto, lo demás no me funciona.
Me encuentro con que precisaría construir una macro que generase una tabla dinámica de acuerdo con los siguientes requisitos:
1-Un libro excel con dos hojas. La hoja de los datos origen siempre posee el mismo nombre, pero su contenido (nº filas, nºcolumnas y fila de rótulos) es variable. En concreto, en la hoja origen pego un listado de datos y genero una nueva tabla dinámica. Borro los datos de la hoja de datos, introduzco otros y genero otra tabla, etc.
2-La tabla dinámica debe generarse en una hoja que debe mantener siempre el mismo nombre.
3-Los criterios de la tabla se reducen a dos: en filas una sola selección, y en datos una sola selección de contar nº de otros datos. Problema: poder introducir en cada ocasión el rótulo de columna para seleccionar y el rótulo para contar (como dije, la hoja origen es variable en cuanto a su contenido). P.ej. Con un InputBox.
He estado trabajando con la grabadora, buscando apuntes, etc. Pero tras bastantes horas he no logro construir la susodicha macro.
Ruego tu ayuda, dado que esta macro sería el complemento de otras varias, pero sin solucionar esto, lo demás no me funciona.
1 Respuesta
Respuesta de Elsa Matilde
1
Para desarrollar una macro se requieren como habrás visto ya, de las referencias: nbre de hojas, filas y col, rango de datos, etc.
Así que si de nada te servirá una macro que yo genere en un libro mío.
Realizá todos los pasos que me explicás con la grabadora, luego la probas, y me la enviás con los comentarios de cuáles son los ajustes que necesita para que cumpla con tus requisitos.
Espero tus noticias.
Así que si de nada te servirá una macro que yo genere en un libro mío.
Realizá todos los pasos que me explicás con la grabadora, luego la probas, y me la enviás con los comentarios de cuáles son los ajustes que necesita para que cumpla con tus requisitos.
Espero tus noticias.
Buenas noches Elsa: ante todo muchas gracias por tu atención.
Te copio los dos primeros pasos de la macro . Los pasos posteriores (renombrar las hojas generadas, crear un libro independiente para cada hoja y guardarlos en una carpeta especificada), ya los tengo resueltos y desarrollados. El problema (para mi insalvable) lo tengo en la primera parte ('Hacer una tabla dinámica).
Lo que transcribo es lo que me sale con la grabadora (y en lo que no sé avanzar).
Punto de partida: en la hoja "LISTADO PRINCIPAL" (vacía) pego un listado (cambiante). Con esta macro pretendo obtener tantos libros diferentes, como criterios distintos de selección de una columna existan.
Tal y como te señalaba en mi primera comunicación desearía:
1-Que el rango de la tabla dinámica (en este ejemplo "'LISTADO PRINCIPAL'!R1C1:R14349C23") se "autoajuste" a la extensión del listado (cambiante en nº de filas y nº de columnas) que pego en la hoja "Listado principal". Eso sí, esta hoja siempre se denomina "LISTADO PRINCIPAL".
2-Que pueda introducir manualmente en cada ejecución el parámetro deseado en AddFields RowFields:="RESPONSABLE" (en este caso es la columna rotulada así, pero en otras será otra columna con otro nombre). Sería suficiente con poder introducir el nº de columna.
3-Que pueda introducir manualmente en cada ejecución el parámetro PivotFields("REFERENCIA") y .Caption = "Cuenta de REFERENCIA" (unas veces será el contenido de esta columna y otras será el de otra columna). Sería suficiente con poder introducir el nº de columna.
En todo caso siempre se toma un solo criterio de selección y un sólo criterio de "cuenta de".
Private Sub SACAR_DINAMICA()
'
' HACER UNA TABLA DINÁMICA
'
'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'LISTADO PRINCIPAL'!R1C1:R14349C23").CreatePivotTable TableDestination:="", _
TableName:="Tabla dinámica1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("Tabla dinámica1").AddFields RowFields:="RESPONSABLE"
With ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("REFERENCIA")
.Orientation = xlDataField
.Caption = "Cuenta de REFERENCIA"
.Function = xlCount
End With
'RENOMBRAR TABLA DINÁMICA COMO "DINÁMICA BASE"
Sheets("Hoja1").Select
Range("D1").Select
ActiveCell.FormulaR1C1 = "DINAMICA BASE"
ActiveSheet.Name = Range("D1").Value
Range("E1").Select
End Sub
Private Sub SACAR_UNA_HOJA_POR_DATO()
'CREAR UNA HOJA POR DATO FILTRADO: BUSCAR ÚLTIMA CELDA CON DATOS (EN LA DINÁMICA)
'GENERAR UNA HOJA POR DATO
Application.ScreenUpdating = False
Sheets("DINAMICA BASE").Select
u = Application.CountA(Range("B:B"))
For I = u To 3 Step -1
Cells(I + 2, 2).Select
'EJECUTAR
Sheets("DINAMICA BASE").Select
Selection.ShowDetail = True
Sheets("DINAMICA BASE").Select
Next
Application.ScreenUpdating = True
End Sub
...
Reiterarte mi agradecimiento por tu atención.
Te copio los dos primeros pasos de la macro . Los pasos posteriores (renombrar las hojas generadas, crear un libro independiente para cada hoja y guardarlos en una carpeta especificada), ya los tengo resueltos y desarrollados. El problema (para mi insalvable) lo tengo en la primera parte ('Hacer una tabla dinámica).
Lo que transcribo es lo que me sale con la grabadora (y en lo que no sé avanzar).
Punto de partida: en la hoja "LISTADO PRINCIPAL" (vacía) pego un listado (cambiante). Con esta macro pretendo obtener tantos libros diferentes, como criterios distintos de selección de una columna existan.
Tal y como te señalaba en mi primera comunicación desearía:
1-Que el rango de la tabla dinámica (en este ejemplo "'LISTADO PRINCIPAL'!R1C1:R14349C23") se "autoajuste" a la extensión del listado (cambiante en nº de filas y nº de columnas) que pego en la hoja "Listado principal". Eso sí, esta hoja siempre se denomina "LISTADO PRINCIPAL".
2-Que pueda introducir manualmente en cada ejecución el parámetro deseado en AddFields RowFields:="RESPONSABLE" (en este caso es la columna rotulada así, pero en otras será otra columna con otro nombre). Sería suficiente con poder introducir el nº de columna.
3-Que pueda introducir manualmente en cada ejecución el parámetro PivotFields("REFERENCIA") y .Caption = "Cuenta de REFERENCIA" (unas veces será el contenido de esta columna y otras será el de otra columna). Sería suficiente con poder introducir el nº de columna.
En todo caso siempre se toma un solo criterio de selección y un sólo criterio de "cuenta de".
Private Sub SACAR_DINAMICA()
'
' HACER UNA TABLA DINÁMICA
'
'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'LISTADO PRINCIPAL'!R1C1:R14349C23").CreatePivotTable TableDestination:="", _
TableName:="Tabla dinámica1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("Tabla dinámica1").AddFields RowFields:="RESPONSABLE"
With ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("REFERENCIA")
.Orientation = xlDataField
.Caption = "Cuenta de REFERENCIA"
.Function = xlCount
End With
'RENOMBRAR TABLA DINÁMICA COMO "DINÁMICA BASE"
Sheets("Hoja1").Select
Range("D1").Select
ActiveCell.FormulaR1C1 = "DINAMICA BASE"
ActiveSheet.Name = Range("D1").Value
Range("E1").Select
End Sub
Private Sub SACAR_UNA_HOJA_POR_DATO()
'CREAR UNA HOJA POR DATO FILTRADO: BUSCAR ÚLTIMA CELDA CON DATOS (EN LA DINÁMICA)
'GENERAR UNA HOJA POR DATO
Application.ScreenUpdating = False
Sheets("DINAMICA BASE").Select
u = Application.CountA(Range("B:B"))
For I = u To 3 Step -1
Cells(I + 2, 2).Select
'EJECUTAR
Sheets("DINAMICA BASE").Select
Selection.ShowDetail = True
Sheets("DINAMICA BASE").Select
Next
Application.ScreenUpdating = True
End Sub
...
Reiterarte mi agradecimiento por tu atención.
Necesitarás algunas variables para guardar los datos que ingresarás manualmente (como contenido de celdas o por InputBox).
Te dejo un ejemplo sobre un modelo mío con el que podrás armar tu rutina. Si falta algo volveme a escribir.
crit = InputBox ("Ingrese col criterio")
'podría ser el contenido de la celda activa
crit=Activecell.value
fila = ActiveSheet.Range("A65536").End(xlUp).Row
'obtengo el índice del nombre de tabla
col = ActiveSheet.Range("IV1").End(xlToLeft).Column
nbre = ActiveSheet.PivotTables(ActiveSheet.PivotTables.Count).Name
nrotd = Mid(nbre, 15, Len(nbre))
nbre = "Tabla dinámica" & nrotd + 1
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Listado!R1C1:R" & fila & "C" & col).CreatePivotTable TableDestination:= _
"[FICHAJES.xls]Listado!R11C7", TableName:=nbre, DefaultVersion _
:=xlPivotTableVersion10
ActiveSheet.PivotTables(nbre).AddFields RowFields:=crit, _
ColumnFields:="DIA"
De la misma manera podés indicar la celda de destino y otros campos como:
.Caption = "Cuenta de " & crit
Te dejo un ejemplo sobre un modelo mío con el que podrás armar tu rutina. Si falta algo volveme a escribir.
crit = InputBox ("Ingrese col criterio")
'podría ser el contenido de la celda activa
crit=Activecell.value
fila = ActiveSheet.Range("A65536").End(xlUp).Row
'obtengo el índice del nombre de tabla
col = ActiveSheet.Range("IV1").End(xlToLeft).Column
nbre = ActiveSheet.PivotTables(ActiveSheet.PivotTables.Count).Name
nrotd = Mid(nbre, 15, Len(nbre))
nbre = "Tabla dinámica" & nrotd + 1
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Listado!R1C1:R" & fila & "C" & col).CreatePivotTable TableDestination:= _
"[FICHAJES.xls]Listado!R11C7", TableName:=nbre, DefaultVersion _
:=xlPivotTableVersion10
ActiveSheet.PivotTables(nbre).AddFields RowFields:=crit, _
ColumnFields:="DIA"
De la misma manera podés indicar la celda de destino y otros campos como:
.Caption = "Cuenta de " & crit
Elsa: ejecuto tu ejemplo paso a paso, pero al llegar a la línea nbre = ActiveSheet.PivotTables(ActiveSheet.PivotTables.Count).Name me sale error 1004 "No se puede obtener la propiedad PivotTables de la clase Worksheet"
Siento abusar de tu paciencia.
Siento abusar de tu paciencia.
Es porque todavía no tenés tablas en la hoja.
Ajustá esto. Las lineas negras ya las tenés, reemplazá lo que va entre ellas por estas.
col = ActiveSheet.Range("IV1").End(xlToLeft).Column
If ActiveSheet.PivotTables.Count = 0 Then
nrotd = 0
Else
nbre = ActiveSheet.PivotTables(ActiveSheet.PivotTables.Count).Name
nrotd = Mid(nbre, 15, Len(nbre))
End If
nbre = "Tabla dinámica" & nrotd + 1
No lo probé así que si luego te surge algún otro error, volveme a escribir (con la rutina resultante para esta parte)
Ajustá esto. Las lineas negras ya las tenés, reemplazá lo que va entre ellas por estas.
col = ActiveSheet.Range("IV1").End(xlToLeft).Column
If ActiveSheet.PivotTables.Count = 0 Then
nrotd = 0
Else
nbre = ActiveSheet.PivotTables(ActiveSheet.PivotTables.Count).Name
nrotd = Mid(nbre, 15, Len(nbre))
End If
nbre = "Tabla dinámica" & nrotd + 1
No lo probé así que si luego te surge algún otro error, volveme a escribir (con la rutina resultante para esta parte)
Elsa: me parece que soy un alumno algo torpe. He estado dándole vueltas y revisando, pero mis conocimientos no dan para más.
Así ha quedado:
crit = InputBox("Ingrese col criterio")
'podría ser el contenido de la celda activa
crit = ActiveCell.Value
fila = ActiveSheet.Range("A65536").End(xlUp).Row
'obtengo el índice del nombre de tabla
col = ActiveSheet.Range("IV1").End(xlToLeft).Column
'Modificacion
If ActiveSheet.PivotTables.Count = 0 Then
nrotd = 0
Else
nbre = ActiveSheet.PivotTables(ActiveSheet.PivotTables.Count).Name
nrotd = Mid(nbre, 15, Len(nbre))
End If
'Fin modificacion
nbre = "Tabla dinámica" & nrotd + 1
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Listado!R1C1:R" & fila & "C" & col).CreatePivotTable TableDestination:= _
"[FICHAJES.xls]Listado!R11C7", TableName:=nbre, DefaultVersion _
:=xlPivotTableVersion10
ActiveSheet.PivotTables(nbre).AddFields RowFields:=crit, _
ColumnFields:="DIA"
Ejecuto paso a paso, y al llegar a:
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Listado!R1C1:R" & fila & "C" & col).CreatePivotTable TableDestination:= _
"[FICHAJES.xls]Listado!R11C7", TableName:=nbre, DefaultVersion _
:=xlPivotTableVersion10
Aquí sale el mensaje "Se ha producido el error '5' en tiempo de ejecución: Argumento o llamada a procedimiento no válida".
De nuevo tengo que abusar de tu paciencia
Así ha quedado:
crit = InputBox("Ingrese col criterio")
'podría ser el contenido de la celda activa
crit = ActiveCell.Value
fila = ActiveSheet.Range("A65536").End(xlUp).Row
'obtengo el índice del nombre de tabla
col = ActiveSheet.Range("IV1").End(xlToLeft).Column
'Modificacion
If ActiveSheet.PivotTables.Count = 0 Then
nrotd = 0
Else
nbre = ActiveSheet.PivotTables(ActiveSheet.PivotTables.Count).Name
nrotd = Mid(nbre, 15, Len(nbre))
End If
'Fin modificacion
nbre = "Tabla dinámica" & nrotd + 1
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Listado!R1C1:R" & fila & "C" & col).CreatePivotTable TableDestination:= _
"[FICHAJES.xls]Listado!R11C7", TableName:=nbre, DefaultVersion _
:=xlPivotTableVersion10
ActiveSheet.PivotTables(nbre).AddFields RowFields:=crit, _
ColumnFields:="DIA"
Ejecuto paso a paso, y al llegar a:
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Listado!R1C1:R" & fila & "C" & col).CreatePivotTable TableDestination:= _
"[FICHAJES.xls]Listado!R11C7", TableName:=nbre, DefaultVersion _
:=xlPivotTableVersion10
Aquí sale el mensaje "Se ha producido el error '5' en tiempo de ejecución: Argumento o llamada a procedimiento no válida".
De nuevo tengo que abusar de tu paciencia
pero ya hemos avanzado 1 paso ... je je
Lo armaré en algún libro mío. Mañana te estaré escribiendo.
Lo armaré en algún libro mío. Mañana te estaré escribiendo.
Te dejo la parte de la rutina que va entre tus líneas (ver día 4/05):
'HACER UNA TABLA DINÁMICA
' Macro desarrollada el 12/05/2008 por Elsamatilde
'
Crit1 = ActiveCell 'obtiene el RowFields - reemplazar por otra forma de obtenerlo
crit2 = ActiveCell.Offset(0, 1) 'obtiene el PivotFields - reemplazar por Inputbox y otro medio
fila = ActiveSheet.Range("A65536").End(xlUp).Row
col = ActiveSheet.Range("IV1").End(xlToLeft).Column
If ActiveSheet.PivotTables.Count = 0 Then
nrotd = 0
Else
nbre = ActiveSheet.PivotTables(ActiveSheet.PivotTables.Count).Name
nrotd = Mid(nbre, 15, Len(nbre))
End If
nbre = "Tabla dinámica" & nrotd + 1
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"LISTADO!R1C1:R" & fila & "C" & col).CreatePivotTable TableDestination:="", _
TableName:=nbre, DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables(nbre).AddFields RowFields:=crit1
With ActiveSheet.PivotTables(nbre).PivotFields(crit2)
.Orientation = xlDataField
.Caption = "Cuenta de " & crit2
.Function = xlCount
End With
'RENOMBRAR TABLA DINÁMICA COMO DINÁMICA.....
Atención que mi hoja se llama LISTADO solamente.- Ajustar
Cualquier dificultad podes solicitarme el libro a mi correo.
'HACER UNA TABLA DINÁMICA
' Macro desarrollada el 12/05/2008 por Elsamatilde
'
Crit1 = ActiveCell 'obtiene el RowFields - reemplazar por otra forma de obtenerlo
crit2 = ActiveCell.Offset(0, 1) 'obtiene el PivotFields - reemplazar por Inputbox y otro medio
fila = ActiveSheet.Range("A65536").End(xlUp).Row
col = ActiveSheet.Range("IV1").End(xlToLeft).Column
If ActiveSheet.PivotTables.Count = 0 Then
nrotd = 0
Else
nbre = ActiveSheet.PivotTables(ActiveSheet.PivotTables.Count).Name
nrotd = Mid(nbre, 15, Len(nbre))
End If
nbre = "Tabla dinámica" & nrotd + 1
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"LISTADO!R1C1:R" & fila & "C" & col).CreatePivotTable TableDestination:="", _
TableName:=nbre, DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables(nbre).AddFields RowFields:=crit1
With ActiveSheet.PivotTables(nbre).PivotFields(crit2)
.Orientation = xlDataField
.Caption = "Cuenta de " & crit2
.Function = xlCount
End With
'RENOMBRAR TABLA DINÁMICA COMO DINÁMICA.....
Atención que mi hoja se llama LISTADO solamente.- Ajustar
Cualquier dificultad podes solicitarme el libro a mi correo.
