Macro to Create Pivot Table

This VBA macro builds a PivotTable from your data on a new sheet, with the fields you choose, and a second macro refreshes every PivotTable in the workbook.

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Create a PivotTable

Put your data on a sheet starting at A1, with headers such as Region and Sales. Select that sheet and run:

Sub CreatePivot()
    Dim src As Range, ws As Worksheet, pt As PivotTable
    Set src = ActiveSheet.Range("A1").CurrentRegion
    Set ws = Worksheets.Add
    Set pt = ActiveWorkbook.PivotCaches.Create(xlDatabase, src.Address(External:=True)) _
        .CreatePivotTable(ws.Range("A3"), "SalesPivot")
    pt.PivotFields("Region").Orientation = xlRowField
    pt.AddDataField pt.PivotFields("Sales"), "Total Sales", xlSum
End Sub

Change "Region" and "Sales" to your own column headers. The names must match exactly.

Add a column field and a filter

Add these lines before End Sub to split by month and filter by product:

pt.PivotFields("Month").Orientation = xlColumnField
    pt.PivotFields("Product").Orientation = xlPageField

Refresh every PivotTable

Sub RefreshAllPivots()
    Dim ws As Worksheet, pt As PivotTable
    For Each ws In Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws
End Sub

Or use Data → Refresh All (Ctrl + Alt + F5), which also refreshes queries and connections.

Without a macro

1. Click any cell in the data and go to Insert → PivotTable.
2. Click OK, then drag fields into Rows, Columns and Values.

Things to know

• Turn the source data into an Excel Table first. The PivotTable then picks up new rows when you refresh.
• If a field name doesn’t match a header, the macro stops with an error. Check spelling and spaces.
• In Excel 365, the PIVOTBY function builds a pivot-style summary with a formula.

Related macros