Problem: Create multiple pivot tables from single source sheet

Hi,

I have one source sheet with data and I want to create multiple pivot tables based on it with varying filters.

This normally works fine, but when trying to create the same pivot table twice it causes this error: "Run-time error '5': Invalid procedure or argument"

The debugger marks this line: Set pvt = Worksheets(SheetName).PivotTables.Add(pvtCache, Range(StartPvt), PivotName)

I have tried deleting the worksheet containing the pivot table and then constructing it again, but it doesn't seem to help.

Do you have some suggestion why this error is caused and how to prevent it?

If you delete the sheet containing the Pivot Table, the Pivot Cache also gets deleted. Make sure you create a new Pivot Cache before creating a new Pivot Table. And if you don't delete the Pivot Sheets, check if a Pivot Cache exists and use it as a source Pivot Cache in the Pivot Table creation otherwise create a new Pivot Cache first.

Hi,

It would be useful to see more code. Are you trying to reuse the same name for another pivot table, or trying to put it over an existing pivot table?

If you want to create a Pivot Table based on Pivot Cache of an existing Pivot Table, try something like this...

Dim ws As Worksheet, wsPivot As Worksheet, wsNewPivot As Worksheet
Dim pvtCache As PivotCache
Dim PT As PivotTable, newPT As PivotTable
Set wsPivot = Sheets("Pivot Sheet") 'Sheet which contains the existing Pivot Table
Set PT = wsPivot.PivotTables(1)
Set pvtCache = PT.PivotCache
Set wsNewPivot = Sheets.Add(after:=Sheets(Sheets.Count))

Set newPT = pvtCache.CreatePivotTable(wsNewPivot.Range("A3"))

Yes- you should be able to use pvtCache.CreatePivotTable