Skip to content
Learni
View all tutorials
VBA & Automatisation

How to Create Pivot Tables with VBA in 2026

18 minINTERMEDIATE
ExcelVBATableaux croisés
Lire en français

Introduction

Pivot tables are essential for analyzing large amounts of data in Excel. Automating their creation with VBA saves considerable time and helps standardize your reports. This intermediate tutorial shows you how to generate, configure, and refresh a pivot table using robust VBA code. You will learn to manipulate the PivotCache and fields professionally. Each step includes complete, working code that you can copy directly into your macros.

Prerequisites

  • Excel 2016 or later
  • Basic VBA knowledge (modules, variables)
  • An Excel workbook containing tabular data
  • The Developer tab enabled in Excel

Initialize the workbook and data

Module1.bas
Sub CreerTCD()
    Dim wb As Workbook
    Dim wsData As Worksheet
    Set wb = ThisWorkbook
    Set wsData = wb.Sheets("Donnees")
    ' Check that data exists
    If wsData.Range("A1").Value = "" Then
        MsgBox "Aucune donnée trouvée", vbExclamation
        Exit Sub
    End If
End Sub

This code initializes the active workbook and the source data sheet. It includes a basic check to avoid errors when no data is present.

Define the source range

Module1.bas
    Dim lastRow As Long
    Dim lastCol As Long
    Dim sourceRange As Range
    With wsData
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        Set sourceRange = .Range(.Cells(1, 1), .Cells(lastRow, lastCol))
    End With

This section dynamically calculates the source data range using End(xlUp) to automatically adapt to the table size.

Create the PivotCache

Module1.bas
    Dim pivotCache As PivotCache
    Set pivotCache = wb.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=sourceRange)

The PivotCache is created from the source range. It stores the data in memory and enables fast refreshing of the pivot table.

Create the pivot table

Module1.bas
    Dim wsPivot As Worksheet
    Dim pivotTable As PivotTable
    Set wsPivot = wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count))
    wsPivot.Name = "TCD_Automatique"
    Set pivotTable = pivotCache.CreatePivotTable( _
        TableDestination:=wsPivot.Range("A3"), _
        TableName:="MonTCD")

A new worksheet is created to host the pivot table. The CreatePivotTable method positions the table at cell A3 with an explicit name.

Add fields to the pivot table

Module1.bas
    With pivotTable
        .PivotFields("Region").Orientation = xlRowField
        .PivotFields("Produit").Orientation = xlColumnField
        .PivotFields("Ventes").Orientation = xlDataField
        .PivotFields("Ventes").Function = xlSum
    End With

Fields are positioned as rows, columns, and values. The summary function is set to Sum for sales.

Best practices

  • Always explicitly name tables and caches
  • Use variables for dynamic ranges
  • Refresh the pivot table after modifying source data
  • Protect code against empty range errors
  • Document field names for maintenance

Common errors to avoid

  • Forgetting to define the complete source range
  • Not handling cases where the pivot sheet already exists
  • Using absolute references instead of End(xlUp)
  • Ignoring data type errors in fields

To go further

Discover our advanced training on Excel and VBA automation: https://learni-group.com/formations