Grouped and Ungrouped Dates from Same Pivot Table Source

In some workbooks, you might create two or more pivot tables that are based source data. Even if they are on different worksheets, those pivot tables will share some features, such as calculated items and grouped fields.

 

So, if you group the dates in one pivot table, the same grouping will show up in the other pivot table. This occurs because the pivot tables share the same pivot cache. You can manually create a separate pivot cache, by copying one of the pivot tables to a different workbook, temporarily. See the instructions here.

Note: If you create a separate pivot cache for one of the pivot tables, the file size might increase substantially. To reduce the effect, you can turn off the option to save the source data with the file.

 

Create New Pivot Cache with VBA

Instead of manually creating a separate pivot cache for a pivot table, you can select a cell in the pivot table, and run the following code.

It adds a temporary sheet to the workbook, and creates a new pivot table there, based on the same data source, but in a new pivot cache. The selected pivot table is set to the same pivot cache as the new table, and then the temporary sheet is deleted.

Sub SelPTNewCache()
    Dim wsTemp As Worksheet
    Dim pt As PivotTable
    
    On Error Resume Next
    Set pt = ActiveCell.PivotTable
    
    If pt Is Nothing Then
        MsgBox "Active cell is not in a pivot table"
    Else
        Set wsTemp = Worksheets.Add
        
        ActiveWorkbook.PivotCaches.Create( _
            SourceType:=xlDatabase, _
            SourceData:=pt.SourceData).CreatePivotTable _
            TableDestination:=wsTemp.Range("A3"), _
            TableName:="PivotTableTemp"
        
        pt.CacheIndex = wsTemp.PivotTables(1).CacheIndex
        
        Application.DisplayAlerts = False
        wsTemp.Delete
        Application.DisplayAlerts = True
    End If
    
exitHandler:
        Set pt = Nothing

End Sub

_____________________