Automatiza tus informes con Tablas Dinámicas y Macros en Excel

1. Crear la Tabla Dinámica Base

• Antes de automatizar, necesitas tener una Tabla Dinámica que sirva como base. Si aún no sabes cómo hacerla, te recomiendo mi tutorial en donde te explico 📕 cómo hacer una tabla dinámica en 5 segundos.

2. Grabar Macro para Actualizar la Tabla Dinámica

• Ahora vamos a grabar una Macro que automatice la actualización de la Tabla Dinámica.

1. Activa la pestaña "Desarrollador"

• Ve a "Archivo" > "Opciones" 🡪 "Personalizar cinta de opciones".

• En la lista de la derecha, marca la casilla junto a "Desarrollador" o "Programador" (el nombre puede cambiar dependiendo tu versión de Excel).

• Haz clic en "Aceptar".
• Si no ves la pestaña "Desarrollador" en la cinta de opciones de Excel, necesitas activarla. Arriba de la columna de la que estamos hablando puedes seleccionar "Todas las pestañas" y ahí te aparecerá:

2. Empieza a Grabar la Macro

• Ve a la pestaña "Desarrollador" o "Programador".

• Haz clic en el botón "Grabar macro".

• Se abrirá la ventana "Grabar macro".

3. Configura la Macro

• Nombre de la macro: Dale un nombre descriptivo (por ejemplo, ActualizarTablaDinamica). Importante: Los nombres de las Macros no pueden tener espacios; usa guiones bajos (_) para separar palabras.

• Método abreviado (opcional): Puedes asignar una combinación de teclas para ejecutar la Macro rápidamente (por ejemplo, Ctrl+Alt+A). Cuidado: No uses combinaciones que ya existan en Excel (como Ctrl+C o Ctrl+V).
• Guardar macro en: Elige "Este libro".
• Descripción (opcional): Puedes escribir una breve descripción de lo que hace la Macro.
• Haz clic en "Aceptar". ¡A partir de ahora, Excel está grabando todo lo que hagas!

4. Actualiza la Tabla Dinámica

• Haz clic derecho en cualquier celda dentro de la Tabla Dinámica.
• En el menú que aparece, selecciona "Actualizar".

5. Detén la Grabación

• Vuelve a la pestaña "Desarrollador" o "Programador".
• Haz clic en el botón "Detener grabación" que es donde antes estaba el botón "Grabar macro".

1. Preparación

Base de Datos de Ventas

• Asegúrate de tener una base de datos de ventas con al menos las siguientes columnas:
  • Fecha (con formato de fecha).
  • Región (texto).
  • Producto (texto).
  • Ventas (número. 📕 Aprende a aplicar formato de moneda en Excel y Google Sheets).
• Abre el archivo de Excel que contiene la base de datos. Si aún no tienes una base de datos, puedes aprender 📕 cómo crear una base de datos bien estructurada en Excel con mi guía.
• Activa la pestaña "Desarrollador" o "Programador" (si no la tienes activada, sigue las instrucciones del ejemplo anterior).

2. Grabar Macro

Pasos para Grabar la Macro

• Ve a la pestaña "Desarrollador" o "Programador" y haz clic en "Grabar macro".
• Nombra la Macro: CrearInformeMensual.
• Guarda la macro en: "Este libro".
• Haz clic en "Aceptar".
• Ahora, realiza manualmente los siguientes pasos (Excel los está grabando):
  1. Selecciona cualquier celda dentro de tu base de datos de ventas.
  2. Inserta 📕 una tabla dinámica (pestaña "Insertar" 🡪 "Tabla dinámica").
  
  1. Elige crear la tabla dinámica en una "Nueva hoja de cálculo".
  2. En el panel "Campos de tabla dinámica":
     1. Arrastra Región a "Filas".
     2. Arrastra Producto a "Columnas".
     3. Arrastra Ventas a "Valores".
  
  1. Haz clic derecho en cualquier celda de la columna de fechas (si tienes una en tu Tabla Dinámica).
     1. Selecciona "Agrupar"...
     
     1. Elige agrupar por "Meses" y "Años".
  2. Haz clic derecho en cualquier celda de la columna "Meses".
     • Escoge, Meses, es igual a, y escribe la función =HOY().
  3. Selecciona "Filtro", luego, "Filtro de valor".
  4. Configura el filtro:
     • Haz clic derecho en cualquier celda con un valor de venta (en el área de valores).
     • Selecciona "Formato de número"...
  
  1. Elige "Moneda" y ajusta las opciones de formato (decimales, símbolo) como prefieras.
  2. Ve a la pestaña "Diseño" (en las 📕 Herramientas de tabla dinámica).
     • Elige un estilo de Tabla Dinámica de la galería (el que más te guste). Si quieres aprender más sobre las Tablas Dinámicas, te invito a leer mi 📕 guía completa sobre tablas dinámicas en Excel.
  
  1. Detén la grabación de la Macro (pestaña "Desarrollador" 🡪 "Detener grabación").

1. Abre el Editor de VBA

Acceso al Editor de VBA

• Ve a la pestaña "Desarrollador" o "Programador".

• Haz clic en el botón "Visual Basic". Se abrirá una ventana nueva: el Editor de VBA.

2. Encuentra tu Macro

Localización del Código de la Macro

• En el panel de la izquierda (el "Explorador de proyectos"), busca un elemento que dice "Módulos".
• Haz doble clic en "Módulo1" (o el nombre que tenga el módulo).
  • Si no lo ves, en el menú, haz clic en Ver 🡪 Explorador de proyectos.

• Verás el código de tu Macro en la parte derecha de la ventana. Debería verse algo así:

Sub ActualizarTablaDinamica()

'
' ActualizarTablaDinamica Macro
'
'
ActiveSheet.PivotTables("TablaDinámica1").PivotCache.Refresh

End Sub

3. Descripción del código

Explicación del Código de la Macro

• Sub ActualizarTablaDinamica() : Indica el inicio de la Macro. ActualizarTablaDinamica es el nombre que le diste.
• End Sub: Indica el final de la Macro.
• ActiveSheet.PivotTables("TablaDinámica1").PivotCache.Refresh: Esta es la línea importante. Le dice a Excel:
  • ActiveSheet: "En la hoja activa..."
  • .PivotTables("TablaDinámica1"): "...busca la Tabla Dinámica que se llama 'TablaDinámica1'..." (Si tu Tabla Dinámica tiene otro nombre, cámbialo aquí).
  • .PivotCache: "...accede a su memoria caché (donde guarda los datos)..."
  • .Refresh: "...y actualízala".

4. Mejorar la Macro (Opcional)

Optimización del Código

• Hacerla más genérica: En lugar de referirse a la Tabla Dinámica por su nombre ("TablaDinámica1"), puedes hacer que la Macro funcione con cualquier Tabla Dinámica que esté seleccionada. Cambia la línea importante por esta:

Selection.PivotTable.PivotCache.Refresh

Esto le dice a Excel: "Actualiza la Tabla Dinámica que esté seleccionada actualmente".

• Evitar errores: Si no hay ninguna Tabla Dinámica seleccionada, la Macro anterior dará un error. Para evitarlo, puedes añadir unas líneas para verificar si hay una Tabla Dinámica seleccionada:

Sub ActualizarTablaDinamica()

If TypeName(Selection) = "Range" Then

    If Not Selection.PivotTable Is Nothing Then

        Selection.PivotTable.PivotCache.Refresh

    Else

        MsgBox "Por favor, selecciona una celda dentro de una Tabla Dinámica."

    End If

Else

    MsgBox "Por favor, selecciona una celda dentro de una Tabla Dinámica."

End If

End Sub

• If TypeName(Selection) = "Range" Then: Esta línea verifica si lo que el usuario tiene seleccionado es un rango (es decir que se seleccionó al menos una celda).
• If Not Selection.PivotTable Is Nothing Then: Verifica si lo seleccionado es una Tabla Dinámica.
• MsgBox "Por favor, selecciona una celda dentro de una Tabla Dinámica.": Muestra un mensaje si el usuario no tiene una Tabla Dinámica seleccionada.

5. Guarda los Cambios

Finalización del Proceso

• En el Editor de VBA, haz clic en el icono del disquete (o ve a "Archivo" 🡪 "Guardar").
• Cierra el Editor de VBA.

1. Abre el Editor de VBA

Acceso al Editor de VBA

• Ve a la pestaña "Desarrollador" o "Programador" 🡪 "Visual Basic".

2. Encuentra tu Macro

Localización del Código de la Macro

• En el Explorador de proyectos (a la izquierda), busca el módulo donde se guardó tu Macro (probablemente "Módulo1") y haz doble clic en él.

El código se verá más o menos así:

Sub CrearInformeMensual()

' Declaración de variables (es una buena práctica declarar las variables al principio).

Dim wsDatos As Worksheet ' Hoja donde están los datos.

Dim wsInforme As Worksheet ' Hoja donde se creará el informe.

Dim td As PivotTable ' La tabla dinámica.

Dim nombreHoja As String ' Nombre de la nueva hoja.

Dim fechaHoy As Date 'Variable para la fecha de hoy

Dim mes As Integer 'Variable para el mes

Dim año As Integer 'Variable para el año

' --- 1. Define las hojas de trabajo ---

Set wsDatos = ThisWorkbook.Sheets("Ventas") ' Cambia "Ventas" por el nombre de tu hoja de datos.

' --- 2. Crea la nueva hoja para el informe ---

nombreHoja = Format(Date, "mmmm yyyy") ' Crea el nombre de la hoja (ej: "enero 2024").

On Error Resume Next ' Ignora el error si la hoja ya existe.

Set wsInforme = ThisWorkbook.Sheets(nombreHoja) ' Intenta acceder a la hoja.

If Err.Number  0 Then ' Si la hoja no existe

    Set wsInforme = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) 'Crea la Hoja al final

    wsInforme.Name = nombreHoja 'Asigna el nombre a la Hoja

    Err.Clear 'Limpia el error

End If

On Error GoTo 0 ' Reactiva el manejo de errores.

' --- 3. Elimina la tabla dinámica si ya existe en la hoja del informe ---

For Each td In wsInforme.PivotTables

    td.TableRange2.Clear

Next td

' --- 4. Crea la tabla dinámica ---

Set td = wsInforme.PivotTables.Add(PivotCache:=wsDatos.PivotCaches.Create( _

    SourceType:=xlDatabase, SourceData:=wsDatos.Range("A1").CurrentRegion), _

    TableDestination:=wsInforme.Range("A3"), TableName:="InformeMensual")

' --- 5. Configura los campos de la tabla dinámica ---

With td

    ' Añade los campos.

    With .PivotFields("Región")

        .Orientation = xlRowField ' Región en las filas.

        .Position = 1

    End With

    With .PivotFields("Producto")

        .Orientation = xlColumnField ' Producto en las columnas.

        .Position = 1

    End With

    .AddDataField .PivotFields("Ventas"), "Suma de Ventas", xlSum ' Ventas en el área de valores (suma).

    ' --- 6. Aplica el formato ---

    .ShowTableStyleRowStripes = True

    .TableStyle2 = "PivotStyleMedium9" ' Cambia por el nombre del estilo que quieras.

    With .DataBodyRange

        .NumberFormat = "$#,##0.00" ' Formato de moneda.

    End With

    ' --- 7. Filtra por el mes y año actual ---

    fechaHoy = Date

    mes = Month(fechaHoy)

    año = Year(fechaHoy)

    'Verifica si el campo 'Fecha' existe en la tabla dinámica

    On Error Resume Next

    Set campoFecha = .PivotFields("Fecha")

    On Error GoTo 0

    If Not campoFecha Is Nothing Then 'Si existe el campo 'Fecha'...

        With .PivotFields("Fecha")

            .ClearAllFilters

            .PivotItems("(Blanks)").Visible = False ' Oculta los valores en blanco, para evitar error de incompatibilidad

            'Intenta agrupar por meses y años

            On Error Resume Next

            .DataRange.Cells(1).Group Start:=True, End:=True, Periods:=Array(False, False, False, False, True, True, False) 'Agrupa por Meses y años

            If Err.Number  0 Then

                MsgBox "No se pudo agrupar el campo Fecha. Asegúrate de que todas las celdas en la columna 'Fecha' tengan formato de fecha válido.", vbExclamation

                Exit Sub 'Sale del procedimiento si hay un error

            End If

            On Error GoTo 0

            'Configura los filtros de fecha para el mes y año actual

            .PivotFilters.Add2 Type:=xlDateThisMonth

        End With

    Else 'Si no existe el campo Fecha

        MsgBox "El campo 'Fecha' no se encontró en la tabla dinámica.", vbExclamation

    End If

End With

' --- 8. "Congela" los datos (copia y pega como valores) ---

td.TableRange2.Copy 'Copia toda la tabla dinámica

wsInforme.Range("A3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats ' Pega solo valores y formatos

' --- 9. Ajusta el ancho de las columnas ---

wsInforme.Columns.AutoFit

' --- 10. Muestra un mensaje al usuario ---

MsgBox "Informe mensual creado correctamente en la hoja '"  nombreHoja  "'.", vbInformation, "Informe Creado"

End Sub

3. Descripción del código

Explicación del Código de la Macro

• Declaración de variables: Dim se usa para declarar variables, que son como "cajas" donde guardas información.
• Set: Se usa para asignar un objeto (como una hoja o una Tabla Dinámica) a una variable.
• With ... End With: Permite realizar varias acciones sobre un mismo objeto sin tener que repetir su nombre.
• Comentarios: Las líneas que empiezan con ' son comentarios. Sirven para explicar lo que hace el código, pero Excel los ignora.
• Format(Date, "mmmm yyyy"): Obtiene la fecha actual y la formatea para obtener el nombre del mes y el año.
• On Error Resume Next y On Error GoTo 0: Manejan errores. On Error Resume Next le dice a Excel que continúe ejecutando el código incluso si encuentra un error (en este caso, si la hoja ya existe). On Error GoTo 0 vuelve a activar el manejo normal de errores.
• .PivotTables.Add(...): Crea la Tabla Dinámica.
• .Orientation = xlRowField, .Orientation = xlColumnField, .AddDataField: Configuran los campos de la Tabla Dinámica.
• .TableStyle2 = "PivotStyleMedium9": Aplica un estilo predefinido. Puedes ver los nombres de los estilos en la pestaña "Diseño" de las Herramientas de tabla dinámica.
• .DataBodyRange.NumberFormat = "$#,##0.00": Aplica formato de moneda.
• Filtro de Fecha:
  • Primero obtiene el mes y año actual.
  • Limpia los filtros existentes de ese campo (.ClearAllFilters).
  • Agrupa los datos de fecha por meses y años. Esto hace que aparezcan los filtros de mes y año en la Tabla Dinámica.
  • Utiliza un filtro de fecha dinámica.
• TableRange2.Copy y PasteSpecial Paste:=xlPasteValuesAndNumberFormats: Copia la Tabla Dinámica y la pega como valores (para que los datos no cambien si se modifica la base de datos original).
• MsgBox: Muestra un mensaje al usuario.
• .Autofit: Ajusta el ancho de las columnas.

4. Mejorar la Macro (Opcional)

Optimización del Código

• Seleccionar: Haz clic con el cursor en cualquier parte del código que quieras modificar. Verás que el cursor parpadea, indicando que puedes escribir o borrar texto.
• Escribir/Borrar:
  • Para añadir código, simplemente escribe las nuevas líneas o instrucciones.
  • Para borrar código, usa la tecla "Retroceso" (Backspace) o "Suprimir" (Delete).
  • Para modificar código existente, puedes borrar partes y escribir nuevas, o simplemente editar directamente el texto.
• Copiar y Pegar:
  • Puedes copiar y pegar código dentro del Editor de VBA, usando los atajos de teclado habituales (Ctrl+C para copiar, Ctrl+V para pegar) o el menú contextual (clic derecho).
  • Puedes copiar código de este tutorial (o de cualquier otra fuente) y pegarlo en tu Macro.
• Reemplazar: Copia y pega el código que te proporcioné, reemplazando completamente el código que generó la grabación. Para hacer esto:
  • Selecciona todo el código de la Macro existente (puedes hacer clic al principio, mantener pulsada la tecla Mayús [Shift] y hacer clic al final, o usar Ctrl+A si estás dentro de la ventana de código para seleccionar todo).
  • Presiona la tecla "Suprimir" (Delete) o "Retroceso" (Backspace) para borrar el código existente.
  • Pega el nuevo código (Ctrl+V).
• Guardar: Es muy importante guardar los cambios que hagas en el código. En el Editor de VBA, haz clic en el icono del disquete (o ve a "Archivo" 🡪 "Guardar"). Los cambios se guardan en el archivo de Excel (asegúrate de que sea un archivo .xlsm).

5. Ejemplo Práctico de Edición

Cambiar el Estilo de la Tabla Dinámica

Supongamos que quieres cambiar el estilo de la Tabla Dinámica. En el código, busca la línea que dice:

.TableStyle2 = "PivotStyleMedium9"

• Para cambiar el estilo:
  • Haz clic con el cursor justo después del 9.
  • Borra el 9.
  • Escribe el número o nombre del nuevo estilo que quieres usar (por ejemplo, 15 o "PivotStyleLight16"). Puedes ver los nombres de los estilos en la pestaña "Diseño" de las "Herramientas de tabla dinámica" en Excel. Nota: Los nombres de los estilos distinguen entre mayúsculas y minúsculas.
  • Guarda los cambios (clic en el icono del disquete).

6. Consejos y mejores prácticas

Recomendaciones para Trabajar con Macros

• Trabaja en una copia: Antes de hacer cambios importantes en una Macro que ya funciona, considera hacer una copia de seguridad de tu archivo de Excel. Así, si algo sale mal, siempre puedes volver a la versión anterior.
• Usa comentarios: Añade comentarios a tu código (líneas que empiezan con ') para explicar lo que estás haciendo. Esto te ayudará a entender el código más adelante (y también a otras personas que puedan leerlo).
• Prueba frecuentemente: Después de hacer un cambio en el código, guarda y prueba la Macro para asegurarte de que sigue funcionando como esperas. Es más fácil encontrar y corregir errores si pruebas poco a poco, en lugar de hacer muchos cambios a la vez.
• Usa nombres significativos para tus macros, variables, etc.
• Sangrías: El Editor de VBA intentará aplicar sangría automáticamente a tu código. La sangría ayuda a que el código sea más fácil de leer, al mostrar visualmente la estructura de los bloques If...Then...Else, For...Next, With...End With, etc. Asegúrate de que la sangría sea consistente.

7. Asignando la Macro a un Botón

Para que sea más fácil ejecutar la Macro, vamos a asignarla a un botón que pondremos en la hoja de Excel.

1. Inserta el Botón:

• Ve a la pestaña "Desarrollador".
• En el grupo "Controles", haz clic en el botón "Insertar" (es un icono de una caja de herramientas).
• En la sección "Controles de formulario", elige el primer botón (el que parece un rectángulo).
• Dibuja el botón en tu hoja de Excel, haciendo clic y arrastrando.

2. Asigna la Macro:

• Cuando sueltes el botón del mouse, se abrirá la ventana "Asignar macro".
• Selecciona el nombre de tu Macro (por ejemplo, ActualizarTablaDinamica).
• Haz clic en "Aceptar".

3. Personaliza el Botón (Opcional):

• Haz clic derecho en el botón.
• Selecciona "Modificar texto" para cambiar el texto del botón (por ejemplo, "Actualizar Informe").
• Puedes cambiar el tamaño, la posición y el formato del botón como cualquier otro objeto de Excel.