Creating multiple pivots

[charlie1105]

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