VBA & Macro Problems

Fix Excel VBA Error 5 When Creating a Second PivotTable

Ayan MasoodAyan Masood Nov 20, 2026 869 views

Question details

The user needs to create a second Excel PivotTable from the same source data and apply filters without VBA errors.

Fix Excel VBA Error 5 When Creating a Second PivotTable
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.
Before you start

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.

Solution 1Recommended

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.

1
Declare and assign the objects

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").

2
Create the second table

Use Set pt2 = pc.CreatePivotTable(TableDestination:=dest, TableName:="PivotTable2"). Choose an unused table name and a destination with enough empty space for the PivotTable.

3
Reference the field on the correct table

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.

4
Apply the item filter

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.

Reuse the first PivotCache and reference fields through the second PivotTable
Keep at least one item visible: Excel cannot hide every item in a PivotField. OLAP PivotTables and some filter configurations have different visibility rules; inspect the failing line and field settings if the filter code still raises an error.
Free Microsoft Office alternative

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.

Open and work with Microsoft Excel XLSX and XLS spreadsheetsAnalyze spreadsheet data with PivotTable toolsUse a familiar interface for everyday spreadsheet tasksMove existing files without starting a spreadsheet from scratch
microsoft office alternative - wps office

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.