I've got a piece of code that will create a number of pivot tables, all from the same source data. Which pivot tables are created depends on a number of tick boxes on a form. As the pivot tables are all based on the same data, I'm assuming the most efficient way to do this is to use one pivot cache. However, I'm not too sure how to set up a pivot cache and then several pivot tables separately. The code I've got so far is 'create pivot cache Dim MyPivotCache As PivotCache MySourceRange = ActiveSheet.Name & "!R1C1:R" & LastRow & "C" & LastColumn Set MyPivotCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, MySourceRange) 'create pivot tables If me.Tick1.Value = True Then ActiveWorkbook.PivotCaches(MyPivotCache).CreatePivotTable _ TableDestination:="", TableName:="MyTable", _ DefaultVersion:=xlPivotTableVersion10 But this doesn't seem to work! Any ideas? Cheers Charlie

If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Creating multiple pivots

Thread starter charlie1105
Start date Nov 17, 2007

charlie1105

Board Regular

Nov 17, 2007

I've got a piece of code that will create a number of pivot tables, all from the same source data. Which pivot tables are created depends on a number of tick boxes on a form.

As the pivot tables are all based on the same data, I'm assuming the most efficient way to do this is to use one pivot cache. However, I'm not too sure how to set up a pivot cache and then several pivot tables separately.

The code I've got so far is

Code:

'create pivot cache
Dim MyPivotCache As PivotCache
MySourceRange = ActiveSheet.Name & "!R1C1:R" & LastRow & "C" & LastColumn
Set MyPivotCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, MySourceRange)

'create pivot tables
If me.Tick1.Value = True Then
ActiveWorkbook.PivotCaches(MyPivotCache).CreatePivotTable _
        TableDestination:="", TableName:="MyTable", _
        DefaultVersion:=xlPivotTableVersion10

But this doesn't seem to work!

Any ideas?

Cheers

Charlie