Excel VBA para tablas dinámicas

Las tablas dinámicas son una herramienta clave para que muchos usuarios de Excel analicen datos. Son flexibles y fáciles de usar. Combine el poder de las tablas dinámicas con la automatización de VBA y podremos analizar datos aún más rápido. Esta publicación incluye el código esencial para controlar las tablas dinámicas con VBA.

Tabla de contenido

• Actualizaciones de tablas dinámicas
• Actualizar una tabla dinámica
• Actualizar todas las tablas dinámicas en el libro activo
• Actualizar todas las tablas dinámicas en una hoja de trabajo
• Crear una tabla dinámica
• Eliminar tablas dinámicas
• Eliminar una sola tabla dinámica
• Eliminar todas las tablas dinámicas del libro de trabajo
• Cambiar fuente de tabla dinámica
• Desactivar el ancho de columna de ajuste automático en todas las tablas dinámicas
• Agregar y eliminar columnas, filas y valores a una tabla dinámica
• Agregar campos a una tabla dinámica
• Agregar campo a las columnas de la tabla dinámica
• Agregar campo a los filtros de la tabla dinámica
• Campo de posición en filtros de tabla dinámica
• Eliminar campo de los valores de la tabla dinámica
• Campo de posición en una tabla dinámica
• Agregar campo a la sección de valores de la tabla dinámica
• Eliminar campo de la sección de valores de la tabla dinámica
• Borrar filtros de tablas dinámicas
• Aplicar filtro a la tabla dinámica
• Aplicar múltiples filtros a una tabla dinámica
• Agregar un campo calculado
• Borrar todos los campos de una tabla dinámica

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre de archivo: 0047 Excel VBA para tablas dinámicas.zip

Tener acceso

Actualizaciones de tablas dinámicas

De todas las tareas que realizamos en las tablas dinámicas, actualizar los datos es la más común. Entonces, comencemos por ahí.

Actualizar una tabla dinámica

El siguiente código actualiza una única tabla dinámica llamada PivotTable1. Cambie el valor de la variable pvtName para que sea el nombre de su tabla dinámica.

Sub RefreshAPivotTable()'Crear una variable para contener el nombre de Pivot TableDim pvtName As String'Asignar el nombre de la tabla dinámica a variablepvtName = "PivotTable1"'Actualizar la tabla dinámicaActiveSheet.PivotTables(pvtName).PivotCache.RefreshEnd Sub

Actualizar todas las tablas dinámicas en el libro activo

El siguiente fragmento de código actualiza todas las tablas dinámicas del libro activo.

Sub RefreshAllPivotTables()'Actualizar todas las tablas dinámicasActiveWorkbook.RefreshAllEnd Sub

Actualizar todas las tablas dinámicas en una hoja de trabajo

Podemos actualizar todas las tablas dinámicas de un libro con una sola línea de código. Sin embargo, para actualizar todas las tablas dinámicas en una hoja de trabajo, debemos recorrer y actualizar cada una de ellas.

Sub RefreshAllPivotTablesWorkbook()'Crear una variable para contener hojas de trabajoDim ws como hoja de trabajo'Crear una variable para contener tablas dinámicasDim pvt como tabla dinámica'Recorrer cada hoja de trabajo para cada ws en ActiveWorkbook.Worksheets 'Recorrer cada tabla dinámica para cada pvt en ws.PivotTables ' actualizar la tabla dinámica pvt.RefreshTable Siguiente pvtNext wsEnd Sub

Crear una tabla dinámica

El siguiente código VBA crea una tabla dinámica. Requiere 4 entradas:

• La hoja que contiene los datos de origen.
• El rango que contiene los datos de origen.
• La hoja sobre la que colocar la tabla dinámica
• La referencia de celda donde colocar la tabla dinámica.

Sub CreatePivotTable()'Crea todas las variablesDim pvtCache As PivotCacheDim pvt As PivotTableDim pvtLocationWs As StringDim pvtLocationRng As StringDim pvtSourceWs As StringDim pvtSourceRng As StringDim pvtSourceFull As StringDim pvtLocationFull As String'Establece la hoja y los rangos para las variablespvtSourceWs = "Sheet1"pvtSourceR ng = "A1 :D50"pvtLocationWs = "Sheet2"pvtLocationRng = "A1"'Crea la cadena para la fuentepvtSourceFull = pvtSourceWs "!" _ Range(pvtSourceRng).Address(ReferenceStyle:=xlR1C1)'Crea la cadena para la ubicaciónpvtLocationFull = pvtLocationWs "!" _ Range(pvtLocationRng).Address(ReferenceStyle:=xlR1C1)'Crear el Pivot CacheSet pvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _ SourceData:=pvtSourceFull)'Crear el Pivot TableSet pvt = pvtCache.CreatePivotTable(TableDestination: =pvtLocationFull, _ TableName:="PivotTable1")End Sub

El ejemplo anterior utiliza rangos estándar de Excel. Para usar una tabla de Excel como fuente, podemos usar el nombre de la tabla sin hacer referencia a la hoja.

Eliminar tablas dinámicas

Esta sección contiene dos opciones para eliminar tablas dinámicas.

Eliminar una sola tabla dinámica

La siguiente macro elimina una única tabla dinámica. Cambie el nombre de la variable pvtName por el nombre de la tabla dinámica que desea eliminar.

Sub DeletePivotTable()'Crear una variable para contener el nombre de Pivot TableDim pvtName As String'Asignar el nombre de la tabla dinámica a variablepvtName = "PivotTable1"'Eliminar la tabla dinámica denominada PivotTable1ActiveSheet.PivotTables(pvtName).TableRange2.ClearEnd Sub

Eliminar todas las tablas dinámicas del libro de trabajo

Si bien podemos actualizar todas las tablas dinámicas de una sola vez, para eliminarlas, debemos recorrerlas y eliminarlas.

Sub DeleteAllPivotTable()'Crear una variable para contener hojas de trabajoDim ws As Worksheet'Crear una variable para contener tablas dinámicasDim pvt As PivotTable'Recorrer cada hoja en el libro de trabajo activoPara cada ws en ActiveWorkbook.Worksheets 'Recorrer cada tabla dinámica en la hoja de trabajo para cada pvt En ws.PivotTables 'Desactivar ajuste automático pvt.TableRange2.Clear Siguiente pvtNext wsEnd Sub

Cambiar fuente de tabla dinámica

Esta macro cambia los datos de origen de una tabla dinámica.

Sub ChangePivotTableSource()'Crea todas las variablesDim pvtCache As PivotCacheDim pvt As PivotTableDim pvtName As StringDim pvtSourceWs As StringDim pvtSourceRng As StringDim pvtSourceFull As String'Establece la hoja y los rangos para las variablespvtName = "PivotTable1"pvtSourceWs = "Sheet1"pvtSourceRng = "H1: K4"Set pvt = ActiveSheet.PivotTables(pvtName)'Crea la cadena para el origenpvtSourceFull = pvtSourceWs "!" _ Range(pvtSourceRng).Address(ReferenceStyle:=xlR1C1)'Crear un nuevo Pivot CacheSet pvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _ SourceData:=pvtSourceFull)'Cambiar el caché dinámico, la tabla dinámica apunta a topvt.ChangePivotCache pvtCacheEnd Sub

El ejemplo anterior utiliza rangos estándar de Excel. Para usar una tabla de Excel como fuente, podemos usar el nombre de la tabla sin hacer referencia a la hoja.

Desactivar el ancho de columna de ajuste automático en todas las tablas dinámicas

La siguiente macro cambia la configuración para conservar el ancho de las columnas cuando se actualiza una tabla dinámica.

Sub TurnOffAutoFitColumnsPivotTables()'Crear una variable para contener hojas de trabajoDim ws como hoja de trabajo'Crear una variable para contener tablas dinámicasDim pvt como tabla dinámica'Recorrer cada hoja en el libro de trabajo activoPara cada ws en ActiveWorkbook.Hojas de trabajo'Recorrer cada tabla dinámica en la hoja de trabajo para cada pt En ws.PivotTables 'Desactivar el ajuste automático pvt.HasAutoFormat = False 'Volver a activar el ajuste automático 'pvt.HasAutoFormat = True Siguiente pvtNext wsEnd Sub

Agregar y eliminar columnas, filas y valores a una tabla dinámica

Después de crear una tabla dinámica, debemos agregar/eliminar campos y aplicar filtros para obtener la vista que queremos.

Agregar campos a una tabla dinámica

Sub AddFieldToPivotTableRows()Dim pvt As PivotTableDim pvtFieldName As StringSet pvt = ActiveSheet.PivotTables("PivotTable1")pvtFieldName = "Nombre de cliente"pvt.PivotFields(pvtFieldName).Orientation = xlRowFieldEnd Sub

Agregar campo a las columnas de la tabla dinámica

Sub AddFieldToPivotTableColumns()Dim pvt As PivotTableDim pvtFieldName As StringSet pvt = ActiveSheet.PivotTables("PivotTable1")pvtFieldName = "Nombre de cliente"pvt.PivotFields(pvtFieldName).Orientation = xlColumnFieldEnd Sub

Agregar campo a los filtros de la tabla dinámica

Sub AddFieldToPivotTableFilters()Dim pvt As PivotTableDim pvtFieldName As StringSet pvt = ActiveSheet.PivotTables("PivotTable1")pvtFieldName = "Nombre de cliente"pvt.PivotFields(pvtFieldName).Orientation = xlPageFieldEnd Sub

Campo de posición en filtros de tabla dinámica

Sub PostionFieldInPivotTableFilters()Dim pvt As PivotTableDim pvtFieldName As StringSet pvt = ActiveSheet.PivotTables("PivotTable1")pvtFieldName = "Nombre de cliente"pvt.PivotFields(pvtFieldName).Position = 1End Sub

Eliminar campo de los valores de la tabla dinámica

Sub RemoveFieldFromPivotTableValues()Dim pvt As PivotTableDim pvtFieldName As StringSet pvt = ActiveSheet.PivotTables("PivotTable1")pvtFieldName = "Nombre de cliente"pvt.PivotFields(pvtFieldName).Orientation = xlHiddenEnd Sub

Campo de posición en una tabla dinámica

Sub PostionFieldInPivotTable()Dim pvt As PivotTableDim pvtFieldName As StringSet pvt = ActiveSheet.PivotTables("PivotTable1")pvtFieldName = "Nombre del cliente"pvt.PivotFields(pvtFieldName).Position = 1End Sub

Agregar campo a la sección de valores de la tabla dinámica

Sub AddFieldToPivotTableValues()Dim pvt As PivotTableDim pvtFieldName As StringDim pvtFieldDescription As StringSet pvt = ActiveSheet.PivotTables("PivotTable1")pvtFieldName = "Ingresos"pvtFieldDescription = "Suma de ingresos"pvt.AddDataField pvt.PivotFields(pvtFi campoName), pvtFieldDescription, xlSumEnd Sub

Eliminar campo de la sección de valores de la tabla dinámica

Sub RemoveValueFieldFromPivotTableValues()Dim pvt As PivotTableDim pvtFieldDescription As StringSet pvt = ActiveSheet.PivotTables("PivotTable1")pvtFieldDescription = "Suma de ingresos"pvt.PivotFields(pvtFieldDescription).Orientation = xlHiddenEnd Sub

Borrar filtros de tablas dinámicas

Sub ClearFiltersToPivotTable()Dim pvt As PivotTableDim pvtField As PivotFieldDim pvtFieldName As StringDim pvtName As StringpvtName = "PivotTable1"Set pvt = ActiveSheet.PivotTables(pvtName)pvtFieldName = "Nombre de cliente"Set pvtField = pvt.PivotFields(pvtFi campoName)pvtField.ClearAllFiltersEnd Sub

Aplicar filtro a la tabla dinámica

Sub ApplyFilterToPivotTable()Dim pvt As PivotTableDim pvtField As PivotFieldDim pvtFieldName As StringDim pvtName As StringDim pvtFilter As StringpvtName = "PivotTable1"Set pvt = ActiveSheet.PivotTables(pvtName)pvtFieldName = "Nombre de cliente"Set pvtField = pvt.PivotFi campos(pvtFieldName)pvtFilter = "A"pvtField.ClearAllFilterspvtField.CurrentPage = pvtFilterEnd Sub

Aplicar múltiples filtros a una tabla dinámica

Sub ApplyMultipleFiltersToPivotTable()Dim pvt As PivotTableDim pvtField Como PivotFieldDim pvtItems Como PivotItemsDim pvtFieldName Como StringDim pvtName Como StringDim pvtFilters Como VariantDim i Como IntegerDim j As IntegerpvtName = "PivotTable1"Set pvt = ActiveSheet.PivotTables(pv tName)pvtFieldName = "Nombre del cliente"Establecer pvtField = pvt.PivotFields(pvtFieldName)pvtFilters = "A, B"pvtFilters = Split(pvtFilters, ", ")pvtField.ClearAllFilterspvtField.EnableMultiplePageItems = TrueFor i = 1 A pvtField.PivotItems.Count Para j = LBound(pvtFilters) A UBound( pvtFilters) Si pvtField.PivotItems(i) = pvtFilters(j) Entonces pvtField.PivotItems(i).Visible = True Salir para lo contrario pvtField.PivotItems(i).Visible = False Fin si es el siguiente jNext iEnd Sub

Agregar un campo calculado

Sub AddCalculatedField()Dim pvtName As StringDim pvt As PivotTableDim pvtCalcName As StringDim pvtCalc As StringpvtName = "PivotTable1"Set pvt = ActiveSheet.PivotTables(pvtName)pvtCalcName = "myCalculation1"pvtCalc = "=Campo1+Campo2"pvt.CalculatedFields.Add p nombreCalcvt, pvtCalc, TrueEnd Sub

Borrar todos los campos de una tabla dinámica

Sub ClearAllFieldsFromPivotTable()Dim pvtName As StringDim pvt As PivotTablepvtName = "PivotTable1"Set pvt = ActiveSheet.PivotTables(pvtName)pvt.ClearTableEnd Sub

---