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?

Yes- you should be able to use pvtCache.CreatePivotTable

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

Please Login or Register  to view this content.