Yes. In Excel for Microsoft 365 on Windows, VBA can create a PivotTable, assign its row, column, filter, and value fields, and refresh it when the source data changes. The basic approach is to create a PivotCache from a clean data range, create the PivotTable on a worksheet, and set each field’s orientation in code.
What “set a PivotTable” means in VBA
Excel does not have a single VBA command called “set a PivotTable.” The phrase can mean several related tasks: creating a PivotTable, referring to one that already exists, deciding which fields appear in its layout, changing filters, or refreshing its results. VBA supports all of these through the PivotCache, PivotTable, and PivotField objects.
A PivotTable summarizes records rather than changing the underlying source rows. For example, a sales list with Date, Region, Product, and Sales columns can be summarized by region and product, with Sales summed as the value. In VBA, setting Region to a row field and Sales to a data field produces that arrangement. The same source can support many different layouts without rewriting the original data.
For a simple repeatable report, a macro can make the layout consistent each time it runs. For an existing PivotTable, the macro can instead retrieve it by name and adjust its fields. Which approach is appropriate depends on whether the report should be rebuilt or merely updated.
Prepare the source data first
A PivotTable works best when its source is a rectangular list: one header row followed by records, with one type of information in each column. Give every column a unique, nonblank header. Avoid merged cells, completely blank rows or columns inside the list, and subtotals mixed into the records. A Sales column should contain numeric values if it is to be summed.
The example below assumes a worksheet named Data with headers in row 1 named exactly Region, Product, and Sales, plus at least one data row. It also assumes a blank worksheet named Summary. The source list must be contiguous because the code uses CurrentRegion, which expands outward from A1 until it meets a blank row or column.
- Use a descriptive worksheet name and stable header names; VBA field references must match the headers.
- Keep dates as real Excel dates and amounts as numbers, not values stored as text.
- Consider converting the source list to an Excel Table with Insert > Table. Tables expand as new rows are added, although a macro using a Table’s full range needs to be written to refer to that table explicitly.
Correct source structure matters: VBA cannot reliably turn inconsistent or ambiguous records into a useful summary merely by creating a PivotTable.
Create and configure a PivotTable with VBA
In Excel for Microsoft 365 on Windows, open the Visual Basic Editor from Developer > Visual Basic, insert a standard module, and paste the macro below. If the Developer tab is hidden, enable it in File > Options > Customize Ribbon. The procedure creates a new PivotTable beginning at cell A3 on the existing Summary sheet.
Sub CreateSalesPivot()
' Build a PivotTable from the current data region.
Dim wb As Workbook
Dim wsData As Worksheet
Dim wsReport As Worksheet
Dim sourceRange As Range
Dim cache As PivotCache
Dim pt As PivotTable
Set wb = ThisWorkbook
Set wsData = wb.Worksheets("Data")
Set wsReport = wb.Worksheets("Summary")
Set sourceRange = wsData.Range("A1").CurrentRegion
Set cache = wb.PivotCaches.Create( _
SourceType:=xlDatabase, SourceData:=sourceRange)
Set pt = cache.CreatePivotTable( _
TableDestination:=wsReport.Range("A3"), _
TableName:="SalesPivot")
With pt
.PivotFields("Region").Orientation = xlRowField
.PivotFields("Region").Position = 1
.PivotFields("Product").Orientation = xlColumnField
.AddDataField .PivotFields("Sales"), "Total Sales", xlSum
End With
End Sub
Before running any macro, save a copy of the workbook. VBA actions generally cannot be undone with Ctrl+Z, so a backup gives you a way to recover if a macro changes or overwrites something unexpectedly. Run the procedure from the Visual Basic Editor or through Developer > Macros. This code expects the named sheets and headers to exist and the destination area to be clear; it is not designed to replace an existing PivotTable with the same name.
How the PivotCache and field settings work
A PivotCache stores the data snapshot and connection information that a PivotTable uses to calculate its summary. The line that calls PivotCaches.Create makes a cache from the source range, and CreatePivotTable places a PivotTable based on that cache at the specified destination. Keeping the cache and PivotTable as separate objects is useful: one cache can sometimes be shared by multiple PivotTables, though that is not necessary for this basic example.
Each column header becomes a PivotField. Setting a field’s Orientation determines where it appears: xlRowField for rows, xlColumnField for columns, xlPageField for a report filter, or xlDataField for the values area. The example uses AddDataField for Sales so it can give the measure the caption Total Sales and specify the summary function xlSum.
For instance, to put Region in the filter area instead of the row area, set its orientation to xlPageField. To summarize a numeric field by an average rather than a sum, pass xlAverage as the summary function in AddDataField. Available summary methods depend on the data and PivotTable type. Text fields commonly count records rather than sum them, while a field containing numbers stored as text may not behave as expected.
Set fields on a PivotTable that already exists
If the PivotTable has already been created, retrieve it from its worksheet instead of creating another one. For example, a workbook with a PivotTable named SalesPivot on the Summary sheet can be referenced as Set pt = ThisWorkbook.Worksheets("Summary").PivotTables("SalesPivot"). A macro can then change field orientation or filters on that object.
Be deliberate when changing an existing layout. A field can have only one orientation at a time, and rearranging fields may change the report’s shape. To remove a field from the layout, set its orientation to xlHidden. To control its order among fields in the same area, set its Position after assigning the orientation. If the field is already used as a data field, removing or replacing it may require adjusting the data fields collection as well.
Filters can also be set through VBA, but the exact method depends on whether the field is a report filter, a row or column label filter, or a slicer. A simple report filter can be selected through its PivotField and its CurrentPage property when the field is in the page area. Item captions must match available PivotItems, and filters can fail if the requested item is absent from the current source.
Refresh the results when data changes
A PivotTable does not necessarily recalculate its displayed summary just because source cells changed. To refresh one PivotTable by name, call its RefreshTable method. In the Excel interface, select the PivotTable and choose PivotTable Analyze > Refresh. To update all PivotTables and other workbook connections that support refresh, use Data > Refresh All.
Refreshing updates the cache from its source; it does not always expand a source defined as a fixed cell range to include new rows below that range. That is a common reason newly appended records do not appear. A source formatted as an Excel Table is a practical way to make the data boundary expand as rows are added, but the PivotTable still needs a refresh. Another option is to update the cache’s source range in VBA before refreshing, taking care to include the header row and all intended records.
Refresh timing can matter in a larger macro. If code changes the source data and then refreshes the PivotTable, do those operations in that order. If the source comes from an external connection, refresh can take longer or depend on credentials and network access; code that continues immediately may need to account for asynchronous connections.
Common VBA PivotTable problems
- “Subscript out of range”: A worksheet or PivotTable name in the code does not match the workbook. Check spelling, spaces, and which workbook the macro is using.
- “Unable to get the PivotFields property” or a missing-field error: The header is absent, misspelled, duplicated, blank, or not part of the source range. Check the exact header text and source boundaries.
- Destination or name conflicts: The destination overlaps another PivotTable or occupied cells, or a PivotTable with that name already exists. Use a clear output region and a unique name, or explicitly remove/reuse the old PivotTable.
- Unexpected Count instead of Sum: The source contains text or blanks in a numeric column, or the field was added with a count summary. Clean the source values and specify
xlSumwhen appropriate. - New rows are missing: The PivotTable’s source range did not expand. Use a structured Table source or revise the cache source before refreshing.
For troubleshooting, inspect the source range in the worksheet and verify its headers before editing the VBA. A macro can fail early if its assumptions are not met; checking these inputs is usually more useful than changing field settings at random.
Excel desktop, Mac, and web differences
The code shown targets desktop Excel and uses the Excel VBA object model. Excel for Microsoft 365 on Windows supports running VBA macros. Excel for Microsoft 365 for Mac also supports VBA, but keyboard shortcuts and some platform-specific features can differ; use the Developer tab to open the Visual Basic Editor if the Windows shortcut does not apply. VBA macros cannot run in Excel for the web. A workbook can be opened there, but use desktop Excel to run or edit this macro.
Save a workbook containing VBA as a macro-enabled Excel workbook (.xlsm) if you need to retain the code. Macro security settings may block macros downloaded from the internet or from untrusted locations. Enable macros only when the workbook and code are trusted; changing security settings broadly to run an unknown macro is not a safe workaround.
When to use VBA instead of the interface
Use VBA when the same PivotTable needs to be created or configured repeatedly, when a report must use a predictable field layout, or when PivotTable setup is one step in a larger workbook process. The macro is especially valuable when it prevents repetitive manual configuration across many files.
For a one-off summary, the PivotTable interface is often simpler: select the source data, choose Insert > PivotTable, select a destination, and drag fields in the PivotTable Fields pane. Power Query is a better fit when the central task is importing, cleaning, combining, or reshaping data before reporting. VBA is not automatically faster or more reliable; it adds code that must be maintained as sheet names, headers, and workbook structure change.
Frequently Asked Questions
Can VBA create a PivotTable from scratch?
Yes. Create a PivotCache from a valid source range, call its CreatePivotTable method with a destination cell and unique name, then assign fields and summaries. The example above follows that sequence.
How do I set a field as a row or column in VBA?
Refer to the field by its source header and set its Orientation to xlRowField or xlColumnField. Set its Position if you need to control its order within that area.
How do I refresh a PivotTable with VBA?
Call RefreshTable on the PivotTable object, such as the PivotTable retrieved by its worksheet and name. If source rows were added, first ensure the PivotCache source includes them.
Why does VBA say a PivotTable field does not exist?
The field name normally comes from the source header. Compare the header exactly, including spaces, and verify the header lies inside the range used to create the cache. Blank or duplicate headers can also cause problems.
Can I run PivotTable VBA in Excel Online?
No. Excel for the web does not run VBA macros. Open the workbook in the desktop version of Excel to run the code; the macro-enabled workbook format can preserve the VBA.
Related
- Excel Data Validation Date Picker: Setup and Options
- Excel Data Validation for Dates: Rules and Examples
- Data Validation Checks in Excel: Rules and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Insert Row Below Active Cell: Code and TipsTo insert a complete worksheet row immediately below the active cell in Excel for Microsoft 365 on Windows,…
- Outlook VBA: How to Pull and Export Mail DataTo pull mail data with VBA, use classic Outlook for Windows’ object model to select a folder, loop…
- VBA InputBox Default Value: Set and Use ItIn VBA, set an InputBox’s default value with its third argument: InputBox(prompt, title, default). For example, InputBox("Enter a…
- Excel VBA Union: Combining Multiple Ranges in a Single MacroIn Excel VBA, Union combines two or more ranges on the same worksheet into one Range object, even…
- Excel UserForm Combo Box: Add, Populate, and Use ItAn Excel UserForm combo box is a VBA control that lets someone choose an item from a drop-down…
- VBA MsgBox Location: Where It Appears and How to Control ItA VBA MsgBox has no built-in argument for setting its screen coordinates. In Excel for Microsoft 365 on…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.