VBA Pivot Table en Excel — crear, refrescar, y por qué muestra números viejos

TL;DR — Una tabla dinámica se construye sobre un PivotCache, una copia congelada de los datos de origen. Créala en dos pasos: PivotCaches.Create(xlDatabase, source) y luego .CreatePivotTable(destination). Distribuye el informe fijando el .Orientation de cada campo (xlRowField, xlColumnField, xlPageField) y añade números con AddDataField. La trampa que pilla a todo el mundo: cambias el origen y la tabla dinámica sigue mostrando los números viejos hasta que llamas a pt.RefreshTable. Apunta el caché a una Table y la tabla dinámica crece con tus datos en lugar de congelarse en un rango fijo.

Dim pc As PivotCache, pt As PivotTable

Set pc = ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, SourceData:="tblSales")     ' un nombre de Table -> crece sola
Set pt = pc.CreatePivotTable( _
    TableDestination:=Worksheets("Report").Range("A3"), TableName:="pt_Sales")

pt.PivotFields("Region").Orientation = xlRowField
pt.AddDataField pt.PivotFields("Amount"), "Total Amount", xlSum

pt.RefreshTable                                          ' re-lee el origen tras cualquier cambio

Cualquiera que automatice informes acaba escribiendo una macro que construye una tabla dinámica, y entonces choca con el mismo muro: la tabla está bien el día que la creas y mal cada día después. Añades cien filas de ventas, ejecutas el informe, y los totales no se han movido. Nada dio error. La causa es la única idea que vale la pena retener sobre las tablas dinámicas: una tabla dinámica no resume tus celdas, resume un PivotCache, una copia de los datos hecha en el momento de construirla. Cada rareza de abajo se sigue de ahí: por qué refrescas, por qué importa el rango de origen, por qué dos tablas pueden compartir un mismo caché. Retén «lee una instantánea, no la hoja» y la tabla dinámica deja de sorprenderte.

Lo que aprenderás

• El modelo mental — una tabla dinámica lee un PivotCache, no tus celdas vivas
• La construcción moderna en dos pasos: PivotCaches.Create y luego CreatePivotTable
• Distribuir los campos con los cuatro valores de Orientation, y añadir campos de datos
• La trampa del refresco — por qué tu tabla dinámica muestra números viejos y cómo arreglarlo
• La trampa del rango de origen — apunta el caché a una Table para que la tabla dinámica crezca
• Encontrar, reapuntar y limpiar las tablas dinámicas que un libro ha ido acumulando

El modelo mental: una tabla dinámica lee un caché, no tus celdas

Cuando construyes una tabla dinámica, Excel no la conecta con tu hoja. Toma los datos de origen, los copia en una estructura oculta en memoria llamada PivotCache, y la tabla dinámica visible es solo una vista sobre ese caché. El caché es una fotografía tomada en el instante en que la creaste. Edita el origen después y la fotografía no cambia — que es exactamente por qué la tabla dinámica sigue mostrando números viejos.

Debug.Print pt.PivotCache.SourceData     ' de que se construyo el cache
Debug.Print pt.PivotCache.RecordCount    ' cuantas filas tiene la INSTANTANEA - no la hoja

De aquí salen directas dos consecuencias útiles. Primera, varias tablas dinámicas pueden compartir un mismo caché (construye la segunda a partir de pt.PivotCache en lugar de un PivotCaches.Create nuevo), lo que mantiene el archivo pequeño y las refresca juntas. Segunda, refrescar no es una tarea de mantenimiento opcional — es el paso que vuelve a tomar la fotografía. En cuanto ves el caché como un objeto aparte con su propia copia de los datos, el resto de la API deja de ser un cajón de sastre de métodos y se convierte en una sola historia: construye el caché, da forma a la vista, vuelve a tomar la instantánea.

Construir una tabla dinámica: primero el caché, luego la tabla

El patrón moderno y fiable son dos pasos explícitos. Crea el caché a partir del origen, y luego crea la tabla a partir del caché:

Dim pc As PivotCache, pt As PivotTable

Set pc = ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:="Sales!A1:D1000")

Set pt = pc.CreatePivotTable( _
    TableDestination:=Worksheets("Report").Range("A3"), _
    TableName:="pt_Sales")

SourceData acepta un nombre de Table ("tblSales"), un nombre definido, o una dirección calificada por hoja como cadena — y la forma de dirección es donde vive la trampa del rango de origen, a la que volvemos más abajo. El TableDestination debe ser una celda real de una hoja, y la tabla dinámica necesita sitio para crecer, así que apúntalo a la esquina superior izquierda de un área que por lo demás esté vacía. Ponerle nombre a la tabla dinámica (TableName:="pt_Sales") importa: es como vuelves a alcanzarla con Worksheets("Report").PivotTables("pt_Sales") en una ejecución posterior en lugar de adivinar con PivotTables(1).

Puede que aún veas macros viejas que usan Worksheets("Report").PivotTableWizard ... en una sola llamada. Funciona, pero esconde el caché, no te da un asa limpia sobre él, y es exactamente el código que escupe la grabadora de macros. Prefiere la forma en dos pasos para que el caché — el objeto que de verdad guarda tus datos — sea algo a lo que puedas poner nombre y refrescar.

Distribuir los campos: las cuatro orientaciones

Una tabla dinámica tiene cuatro zonas, y cada campo aterriza en una de ellas a través de su .Orientation. Los campos de fila y de columna forman la cuadrícula; el campo de página es el filtro de informe; los campos de datos son los números que se resumen:

With pt
    .PivotFields("Region").Orientation = xlRowField
    .PivotFields("Month").Orientation = xlColumnField
    .PivotFields("Category").Orientation = xlPageField     ' el filtro de informe
    .AddDataField .PivotFields("Amount"), "Total Amount", xlSum
End With

Aquí muerden dos cosas. Primera, el nombre de un campo debe coincidir con el encabezado de origen exactamente — PivotFields("Ammount") lanza un error de tiempo de ejecución, no una columna en blanco. Segunda, y más insidiosa: el resumen por defecto de un campo de datos es Suma solo cuando toda la columna es numérica. Cuela un valor de texto o un hueco en blanco en una columna de importes y Excel cambia el valor por defecto a Recuento en silencio, y tu informe muestra «cuántos» donde esperabas «cuánto». Por eso añadir campos de datos con un AddDataField ... , xlSum explícito le gana a soltarlos y cruzar los dedos — declaras la función en lugar de heredar una conjetura. Usa xlAverage, xlCount, xlMax y los demás de la misma manera.

La trampa del refresco: por qué tu tabla dinámica muestra números viejos

Este es el bug número uno de las tablas dinámicas, y no es un bug en absoluto — es el caché haciendo su trabajo. Cambias el origen, el caché sigue reteniendo la vieja fotografía, y la tabla dinámica la muestra fielmente:

' Anadiste 200 filas al origen... la tabla no se ha enterado.
pt.RefreshTable            ' re-lee el cache de ESTA tabla desde su origen

pt.PivotCache.Refresh      ' refresca el cache -> actualiza cada tabla que lo comparte
ThisWorkbook.RefreshAll    ' refresca todas las tablas, consultas y vinculos del archivo

RefreshTable vuelve a tomar la instantánea de una tabla. Si varias tablas comparten un caché, PivotCache.Refresh las actualiza todas de golpe. La regla práctica: cualquier macro que cambie los datos debe refrescar las tablas dinámicas que los leen, y cualquier tabla dinámica de la que dependas sin vigilancia debería refrescarse al abrir el libro. Una tabla dinámica que se construye una vez y no se refresca nunca es una captura de pantalla, no un informe — seguirá mostrando el día en que nació. Si alguna vez has enviado por correo un panel «en vivo» que resultó tener una semana de retraso, esta es la razón.

La trampa del rango de origen: apunta el caché a una Table

El segundo fallo silencioso es el rango de origen. Construye el caché desde una dirección fija y las filas del mes que viene caen fuera de él — ni siquiera un refresco las traerá, porque no están en el rango que al caché se le dijo que leyera:

' FRAGIL - las filas nuevas por debajo de la 1000 son invisibles para siempre
Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, "Sales!A1:D1000")

' ROBUSTO - una Table se autoexpande, asi el cache siempre ve cada fila
Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, "tblSales")

Apuntar el caché a una Table (un ListObject) es la solución que hace que todo lo de aguas abajo se automantenga: la Table crece a medida que añades filas, el caché lee la Table entera, y un simple RefreshTable recoge los datos nuevos sin aritmética de rangos. Un rango con nombre dinámico hace el mismo trabajo si no puedes usar una Table. Para reapuntar una tabla dinámica existente a un origen mejor sin reconstruirla, pásale un caché nuevo con ChangePivotCache. Consulta VBA Table para convertir un rango en una Table y VBA Named Range para la alternativa del nombre dinámico.

Encontrar, reapuntar y limpiar tablas dinámicas

Las tablas dinámicas se acumulan, y una ejecución posterior necesita encontrar la que hizo en lugar de apilar una segunda copia encima. Recorre las colecciones para localizarlas o limpiarlas:

Dim ws As Worksheet, pt As PivotTable
For Each ws In ThisWorkbook.Worksheets
    For Each pt In ws.PivotTables
        Debug.Print ws.Name & " ! " & pt.Name & " -> " & pt.PivotCache.SourceData
    Next pt
Next ws

Antes de reconstruir, comprueba si tu tabla dinámica con nombre ya existe y refréscala en lugar de añadir un duplicado; si de verdad quieres una nueva, borra la vieja con pt.TableRange2.Clear (toda el área de la tabla dinámica) para que una nueva construcción tenga espacio limpio. Trata la tabla dinámica como un objeto duradero que buscas por nombre — la misma disciplina que evita que el código de VBA Worksheet escriba en la pestaña equivocada evita que tu macro de informes críe tablas dinámicas en cada ejecución.

Cómo ayuda ExcelMaster

Los errores de tablas dinámicas que cuestan tiempo de verdad son los silenciosos: el informe que no se refrescó nunca, el rango de origen que se paró en la fila 1000 en marzo, el total que se convirtió en un recuento sin avisar porque una celda tenía texto. Cada uno se ejecuta sin error y le entrega a alguien el número equivocado.

ExcelMaster construye tablas dinámicas como lo haría un analista cuidadoso. Pídele «resume las ventas por región y mes», y apunta el caché a una Table para que el origen crezca solo, fija de forma explícita la función de resumen de cada campo de datos en lugar de heredar Recuento, refresca después de escribir, y busca la tabla dinámica por nombre para que reejecutar actualice el informe en lugar de duplicarlo. Tú describes el resumen que quieres; él conecta el caché, los campos y el refresco para que el número siga siendo correcto el mes que viene.

Preguntas frecuentes

¿Cómo creo una tabla dinámica en VBA?

Constrúyela en dos pasos. Primero crea un caché a partir del origen: Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="tblSales"). Luego crea la tabla a partir del caché: Set pt = pc.CreatePivotTable(TableDestination:=Worksheets("Report").Range("A3"), TableName:="pt_Sales"). Apunta SourceData a un nombre de Table para que la tabla dinámica crezca con tus datos, y ponle un nombre a la tabla dinámica para poder encontrarla de nuevo.

¿Por qué mi tabla dinámica de VBA no se actualiza cuando cambian los datos?

Porque una tabla dinámica lee un PivotCache — una copia congelada del origen hecha en el momento de construirla — no tus celdas vivas. Cambiar el origen no toca el caché, así que la tabla dinámica sigue mostrando los números viejos. Llama a pt.RefreshTable para releer una sola tabla, pt.PivotCache.Refresh para cada tabla que comparte el caché, o ThisWorkbook.RefreshAll para todo el archivo tras cualquier cambio.

¿Cómo añado campos a una tabla dinámica en VBA?

Fija el Orientation de cada campo: pt.PivotFields("Region").Orientation = xlRowField, y luego xlColumnField y xlPageField para las zonas de columna y de filtro. Añade números con pt.AddDataField pt.PivotFields("Amount"), "Total Amount", xlSum para controlar tú la función de resumen — si no, Excel adivina, y recurre a Recuento si la columna contiene cualquier celda de texto o en blanco.

¿Cómo evito que el rango de origen de la tabla dinámica se pierda las filas nuevas?

No apuntes el caché a una dirección fija como "Sales!A1:D1000", porque las filas añadidas por debajo no se ven nunca. Convierte el origen en una Table y pasa el nombre de la Table como SourceData (PivotCaches.Create(xlDatabase, "tblSales")); la Table se autoexpande, así que un RefreshTable normal recoge cada fila nueva. Un rango con nombre dinámico también funciona si una Table no es una opción.

¿Cómo recorro todas las tablas dinámicas de un libro?

Anida dos bucles: For Each ws In ThisWorkbook.Worksheets y luego For Each pt In ws.PivotTables. Dentro, pt.Name y pt.PivotCache.SourceData te dicen qué tabla lee qué. Usa el mismo bucle para refrescar cada tabla dinámica, o para comprobar si tu tabla dinámica con nombre ya existe antes de construir un duplicado.

Probado en

Probado en: Excel 365 (Windows 11), VBA 7.1 — última verificación el 02/09/2026.

Guías relacionadas: VBA Table · VBA Chart · VBA Range · VBA Named Range · VBA Worksheet