Nsryjdtyk

Recently, I have been looking for ways to share the same pivot cache among a few pivot table as they all use the same data source. This helps to reduce the excel file size substantially as it would be inefficient to create a new pivot cache of the same data for each and every pivot table.

One solution provided to me was to use a static variable which allows to make a single pivot cache which can be shared by the different sub routines. Here is how it is done.

'creates and returns a shared pivotcache object

Function GetPivotCache(pRange As Range) As PivotCache

    Static pc As PivotCache 'static variables retain their value between calls



    If pc Is Nothing Then 'create if not yet created

        Set pc = ActiveWorkbook.PivotCaches.Create _

                     (SourceType:=xlDatabase, SourceData:=pRange)

    End If

    Set GetPivotCache = pc

End Function

This is the function used to create the share pivot cache among the subroutines. When creating a pivot table, I call this function to check if a pivot cache has already been created. If it has, it will not create a new copy of the pivot cache and would return the one that is already stored in the static variable.

In case any one is wondering how I use it when creating my pivot table, I will provide some more code to better understand how it is utilized.

Sub pivottable1()



    Dim PSheet As Worksheet, DSheet As Worksheet

    Dim PCache As PivotCache

    Dim PTable As PivotTable

    Dim PField As PivotField

    Dim pRange As Range

    Dim LastRow As Long

    Dim LastCol As Long

    Dim PvtTable As PivotTable

    Dim SheetName As String

    Dim PTName As String



    SheetName = "MySheetName1"

    PTName = "PivotTable1"

    On Error Resume Next

    Application.DisplayAlerts = False

    Worksheets(SheetName).Delete

    Sheets.Add After:=ActiveSheet

    ActiveSheet.Name = SheetName

    Application.DisplayAlerts = True



    Set PSheet = Worksheets(SheetName)

    Set DSheet = Worksheets(1)



    LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row

    LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column

    Set pRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)



    Set PCache = GetPivotCache(pRange)  'Here is where I call the function



    Set PTable = PCache.CreatePivotTable _

    (TableDestination:=PSheet.Cells(1, 1), TABLENAME:=PTName)



    Sheets(SheetName).Select

        Set PvtTable = ActiveSheet.PivotTables(PTName)

        'Rows

        With PvtTable.PivotFields("TypeCol")

            .Orientation = xlRowField

            .Position = 1

        End With



        With PvtTable.PivotFields("NameCol")

            .Orientation = xlRowField

            .Position = 2

        End With



        'Columns

        With PvtTable.PivotFields("CategoryCol")

            .Orientation = xlColumnField

            .Position = 1

        End With



        'Values

        PvtTable.AddDataField PvtTable.PivotFields("Values1"), "Value Balance", xlSum

        PvtTable.AddDataField PvtTable.PivotFields("Values2"), "Value 2 Count", xlCount



End Sub

The problems with the current piece of code are:

1. Only works on one data source. Scenario: If I have 12 pivot tables, 6 pivot tables share data source 1, the other 6 pivot tables share data source 2. This function would only allow for 1 pivot cache unless I make a duplicate of the same function.




2. Updates on the data source will not be reflected. Scenario: I execute the macro which creates the pivot cache and pivot table. Then I add 10 new records into my data source and use the same macro to refresh the pivot table. These 10 new records will not be included as it still uses the out dated pivot cache.

These problem arises as the pivot cache can only be created once. Once it is created, it sort of "ignores" changes as there is already pivot cache that has been created.

What changes can be done to the GetPivotCache function to help resolve these problem? How does excel know when to create a new pivot cache? I tried recording a macro of creating pivot tables but I do not see anything which looks like a check whether the pivot cache already exists, in order to create a new one.