Fix Excel VBA Error 5 When Creating a Second PivotTable
Question details
The user needs to create a second Excel PivotTable from the same source data and apply filters without VBA errors.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A VBA procedure creates one PivotTable successfully, then creates another from the same source and filters status fields.
- Observed behavior
- Run-time error 5 occurs during the second PivotTable operation. The user also reported a PivotCache source type mismatch and an error accessing PivotFields.
Open the VBA editor with Alt+F11 and use Debug when the error appears to identify the failing line. Check the first PivotTable's name, the exact source column headers, and an empty destination for the second table.
Reuse the first PivotCache and reference fields through the second PivotTable
Use this approach when both PivotTables are in the same workbook and use the same source data.
Referencing a PivotField through the wrong PivotTable or another object can cause field-access errors. Reusing the first table's cache also avoids creating a separate cache from the same range.
The filtering example below is for a non-OLAP PivotTable. Replace the worksheet names, table names, field name, destination, and item value with those in your workbook.
In the VBA editor, declare explicit variables and point them to the existing table and destination sheet: Dim pt1 As PivotTable, pt2 As PivotTable, pf As PivotField, pi As PivotItem, pc As PivotCache, dest As Range. Then use Set pt1 = ThisWorkbook.Worksheets("Sheet1").PivotTables("PivotTable1"), Set pc = pt1.PivotCache, and Set dest = ThisWorkbook.Worksheets("Sheet2").Range("H3").
Use Set pt2 = pc.CreatePivotTable(TableDestination:=dest, TableName:="PivotTable2"). Choose an unused table name and a destination with enough empty space for the PivotTable.
Use Set pf = pt2.PivotFields("Task_Status"). The name must exactly match the relevant source header. Add any other required fields through pt2.PivotFields using their exact names.
To use Task_Status as a report filter, set pf.Orientation = xlPageField and pf.EnableMultiplePageItems = True. Make items visible with For Each pi In pf.PivotItems: pi.Visible = True: Next pi. If pf.PivotItems.Count > 1, loop again and set pi.Visible = False when pi.Name = "Completed"; replace Completed with the item you want to exclude.

Check the PivotCache source and field names
Use these checks if the error occurs on cache creation or Excel cannot find a PivotField.
Work with PivotTable spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Microsoft Excel formats, making it easier to move everyday spreadsheet work between office suites. It does not fix Excel's VBA errors; check macro compatibility before migrating automated workbooks.

Frequently Asked Questions
Can two Excel PivotTables use the same PivotCache?
Yes. In VBA, obtain the first table's cache with Set pc = pt1.PivotCache, then call pc.CreatePivotTable with a different destination and table name in the same workbook.
Why can't VBA find my PivotField?
Check that the field is referenced through the intended PivotTable, such as pt2.PivotFields("Task_Status"), and that the name exactly matches a header included in its source data.
Why does PivotCaches.Create report a type mismatch?
Check that the source worksheet and range variables are assigned correctly. If passing a Range fails, try its external R1C1 address as the SourceData argument and create the cache in the source workbook.
Why can't I hide a PivotItem?
Excel requires at least one item in a field to remain visible. Check the field's filter settings and data source type; OLAP PivotTables can have different item visibility rules.




