In Excel VBA, a PivotTable’s fields are accessed through its PivotFields collection: set a field’s Orientation to place it in Rows, Columns, Filters, or Values, and use properties such as Position and NumberFormat to configure it. For example, pt.PivotFields("Region").Orientation = xlRowField puts the Region field in the PivotTable’s Rows area.
What VBA pivot table fields are
A PivotTable field represents a column from the PivotTable’s source data, such as Region, Date, Product, or Sales. In the Excel interface, those fields appear in the PivotTable Fields pane, where you can drag them among the Rows, Columns, Filters, and Values areas. VBA gives you access to the same arrangement through the PivotTable object model.
The key distinction is between a source field and the way that field is displayed in a report. A source column named Sales might be used as a value field summarized by Sum, while the visible value heading reads Total Sales. The field’s underlying source name and its displayed caption are related, but they are not always identical. When automating a report, use the exact source field name when retrieving it, and set a separate caption when you want a reader-friendly heading.
Field names come from the headers in the source range or Excel table. Headers should be nonblank and distinct; duplicate or blank headers can be changed by Excel or cause PivotTable creation problems. A small difference in spelling, spacing, or punctuation in a VBA string can also make a field lookup fail.
The four field areas and orientation values
The Orientation property controls where a field appears. These are the common Excel constants:
xlRowFieldplaces the field in the Rows area, often creating the report’s main categories.xlColumnFieldplaces it in Columns, which lays categories across the report.xlPageFieldplaces it in Filters, creating a report filter above the table.xlDataFieldplaces it in Values, where Excel summarizes its data.xlHiddenremoves it from the displayed areas without deleting the source column.
A field’s Position determines its order within its area. Position 1 is first; changing it can change the layout and, for nested row or column fields, the grouping hierarchy. Positions are meaningful within the relevant area, so avoid assuming that a field’s position number is a permanent identifier.
For example, putting Year in Rows and Region in Columns creates a different report structure from putting Region in Rows and Year in Columns. The underlying records do not change; only the way the PivotTable groups and presents them changes.
Add or move fields with VBA
The following macro assumes a worksheet named Report contains a PivotTable named SalesPivot, and that its source has a column headed Region. It places Region in Rows and moves it to the first row-field position. Save a copy of the workbook before running any macro: VBA changes cannot be undone with Ctrl+Z.
Sub PutRegionInRows()
' Place the Region source field in the first row position.
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Set ws = ThisWorkbook.Worksheets("Report")
Set pt = ws.PivotTables("SalesPivot")
Set pf = pt.PivotFields("Region")
pt.ManualUpdate = True
pf.Orientation = xlRowField
pf.Position = 1
pt.ManualUpdate = False
pt.RefreshTable
End Sub
ManualUpdate can reduce repeated layout recalculation while several changes are being made. The final refresh helps update the displayed report, but it does not repair a wrong field name or automatically expand every kind of source. If a macro errors after setting ManualUpdate to True, the PivotTable may be left in manual-update mode; production code should use error handling to restore it.
To place a field in another area, change the orientation constant. To remove it from the report, use xlHidden. Assigning a new orientation moves a field rather than making a second copy of the same source field in another area.
Configure values, summaries, and captions
When a numeric field is placed in Values, Excel usually chooses a summary such as Sum or Count, but a macro can request the calculation explicitly. The example below adds Sales as a sum and gives the value field a readable caption and currency format. It assumes the same worksheet and PivotTable names, and a source field named Sales.
Sub AddSalesTotal()
' Add Sales to Values and format the result.
Dim ws As Worksheet
Dim pt As PivotTable
Dim valueField As PivotField
Set ws = ThisWorkbook.Worksheets("Report")
Set pt = ws.PivotTables("SalesPivot")
Set valueField = pt.AddDataField( _
pt.PivotFields("Sales"), "Total Sales", xlSum)
valueField.NumberFormat = "$#,##0.00"
End Sub
AddDataField returns the value-field object, so its caption and number format can be set directly. Common summary constants include xlSum, xlCount, xlAverage, xlMax, and xlMin. A text field is generally counted rather than summed, and a numeric field may show Count instead of Sum if Excel interprets its source values as text or finds nonnumeric entries. Set the summary deliberately when the report requires a specific calculation.
The number format affects how values look, not their stored meaning. For instance, a currency format does not convert text into numbers, change the source data, or apply a currency conversion. If a source column contains mixed data types, clean that source before expecting a consistent aggregation.
Inspect field names and current layout
When a macro cannot find a field, inspect the names Excel actually exposes. The following example writes each field name and its orientation to the Immediate window in the VBA editor. Open the editor with Alt+F11 on Windows, then show the Immediate window with Ctrl+G. On Mac, use Option+F11 to open the Visual Basic Editor where supported; keyboard mappings can depend on the Mac keyboard and Excel settings. Excel for the web does not run VBA macros.
Sub ListPivotFields()
' Print field names and areas to the Immediate window.
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Set ws = ThisWorkbook.Worksheets("Report")
Set pt = ws.PivotTables("SalesPivot")
For Each pf In pt.PivotFields
Debug.Print pf.Name, pf.Orientation, pf.Position
Next pf
End Sub
The Immediate window is useful for checking exact spelling and determining whether a field is hidden or already assigned to an area. A field’s numeric orientation value is an Excel constant; when reading the output, compare it with the named constants in the VBA editor’s Object Browser or use a conditional in code rather than relying on unexplained numbers.
There is an important naming nuance: a source field can have a displayed caption that has been changed, and a data field can have its own caption, such as Total Sales. If the lookup string fails, check the field list and source headers rather than guessing from the report heading. Also verify that the macro is pointing to the intended worksheet and PivotTable; names are scoped to their parent objects.
Filters, items, and hidden fields
A field in the Filters area is a report-level filter; it is not the same thing as a field’s individual item visibility. For example, putting Region in the Filters area lets a user choose a region for the whole report. Row and column fields can also be filtered by their items, such as hiding a particular category. These operations affect which records are shown in the PivotTable, not the source worksheet’s records.
Item-level filtering through VBA is more sensitive than moving a field. A typical approach uses the field’s PivotItems collection and each item’s Visible property, but Excel can reject a change if it would hide every item. Item names must match the labels shown by the PivotTable, and date or grouped fields can expose labels that differ from the original source values.
Filter behavior also varies with the PivotTable’s source. PivotTables based on external data or the Data Model can have restrictions compared with a conventional range-based PivotTable. For that reason, test filtering code on a copy and on representative data, including cases where the selected item is missing or the source has no matching records.
Refreshes, source changes, and field availability
A PivotTable field list reflects the PivotTable’s source and cache, not simply every column currently visible on a worksheet. If you add a new source column after creating a PivotTable, refreshing may not make that field available when the existing source range or cache does not include the new column. An Excel table is often a useful source because it expands as rows are added, but the PivotTable still needs an appropriate refresh and source configuration.
Use pt.RefreshTable to refresh a particular PivotTable, or use the Excel ribbon’s Data > Refresh All command to refresh workbook connections and PivotTables. Refreshing recalculates from the current source; it does not automatically correct a source range that points to the wrong cells. When changing a PivotTable’s source with VBA, the right method depends on how it was created, including whether it uses a worksheet range, an Excel table, an external connection, or the Data Model.
Refresh timing matters in a macro. If the code tries to access a field before the cache reflects the updated source, the field may not be found. Confirm the PivotTable’s source, refresh it when appropriate, and then inspect the available field names. For connection-based reports, refresh completion and credentials or connection availability can also affect whether current data appears.
Common errors and practical safeguards
The most common failure is a subscript-out-of-range error when a field lookup uses a name that is not present in that PivotTable. Check the header spelling, spaces, worksheet name, PivotTable name, and whether the field is actually part of the source. Another frequent issue is setting an aggregation that does not fit the source values; a column of numbers stored as text may not behave as a numeric measure.
Before automating a production workbook, use these checks:
- Confirm the source headers are unique, nonblank, and stable.
- Confirm the macro refers to the intended worksheet, PivotTable, and source-field names.
- Test the requested orientation and summary on a copy with realistic data.
- Use error handling when changing multiple properties, particularly if using
ManualUpdate. - Save a separate copy before running the macro; Ctrl+Z cannot reverse VBA changes.
Excel for Microsoft 365 on Windows supports these standard PivotTable VBA objects. Excel for Mac also supports VBA, though some object-model features and workflow details can differ. Excel for the web can display and refresh many PivotTables, but it cannot run VBA macros. If the workbook must be automated in a browser, consider Office Scripts or another supported automation route rather than relying on VBA.
Frequently Asked Questions
How do I add a field to a PivotTable with VBA?
Get the PivotTable, retrieve the source field with pt.PivotFields("FieldName"), and set its Orientation to xlRowField, xlColumnField, or xlPageField. For a summarized value, use pt.AddDataField.
How do I add a value field and choose Sum?
Call pt.AddDataField(pt.PivotFields("Sales"), "Total Sales", xlSum), replacing the source name and caption as needed. The source should contain values Excel can treat as numbers for a meaningful sum.
How can I remove a field from a PivotTable?
Set its orientation to xlHidden, for example pt.PivotFields("Region").Orientation = xlHidden. This removes it from the displayed areas; it does not delete the source column.
Why does VBA say a PivotField does not exist?
The lookup name may not exactly match a source header, the PivotTable may use a different source, or the cache may not include a recently added column. Print the PivotTable’s field names to the Immediate window and check the source and object names.
Can Excel for the web run VBA to arrange PivotTable fields?
No. Excel for the web does not run VBA macros. Use desktop Excel to run the VBA, or investigate Office Scripts if browser-based automation is required.
Related
- Data Validation Reference Table in Excel
- Remove Duplicates in Power Query: A Practical Guide
- Power Query Basics: Import, Clean, and Transform Data
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Loop Continue: How to Skip to the Next IterationVBA has no built-in Continue For, Continue Do, or general Continue statement. To skip the rest of the…
- VBA ArrayList: How to Create and Use It in Excel VBAIn VBA, an ArrayList is a resizable, zero-based list that you can create on Windows with CreateObject("System.Collections.ArrayList"). It…
- Microsoft Excel VBA For Loops: Syntax and ExamplesIn Microsoft Excel VBA, a For...Next loop repeats instructions a set number of times, while For Each...Next repeats…
- Excel VBA MsgBox Examples: Buttons, Results, and Tips for MacrosExcel VBA’s MsgBox function displays a message and can return the button the user clicks. For example, use…
- Open Workbook VBA: Excel Methods and ExamplesTo open an Excel workbook with VBA, call Workbooks.Open and provide its full file path. For example, assign…
- Excel Macro Not Working? Find and Fix Common CausesIf an Excel macro is not working, first confirm that the workbook is saved in a macro-enabled format,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.