Multiple Pivot Tables - Single table of source data

[some_evil]

Hi All,

I am trying to generate several pivot tables from one data source table. I have successfully created my first pivot table (A date field, and a water storage facility level reading) and subsequently a graph from this. I have worked out that I need to group my dates as I am supplied a daily reading, but only need monthly average. All worked great.

Now i need to create more pivot tables and graphs. The next one I want is to create one grouped by years. But when I create this new pivot table and change the grouping of the date field to yearly, it also changes the grouping on my first pivot table, which is undoing my work. Does anyone know how to tell excel that these pivots are independant, and I dont want them changing in unison? See screen grab of my source data and where I am up to...

Thanks all.

 

[liveinhope]

This is due to the way pivot tables based on the same source data will , by default, share a cache .

One very annoying consequence of this is what you have just found , particularly as excel creates the 2nd pivot table with the "new" groupings and changes the first one without warning you .

To get around this use a macro to force the new pivot tbale to have its own cache . Follow the link below to see how to do this - I had the same problem until somone on this board told me about the contextures site

http://www.contextures.com/xlPivot11.html#newcache

 

[some_evil]

Thanks heaps LiveInHope!

That worked perfectly, and I now understand the issue Excel is creating by sharing the one cache... I am running Excel 2007, I wonder if they have fixed this for future versions... lets hope so.

For anyone looking for the code, this is what I used:

'If two or more pivot tables are based on the same pivot cache, they will share some features, such as calculated items and grouped fields.
'To create a separate pivot cache for a pivot table, you can select a cell in the pivot table, and then run the following code.
'The code adds a 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

 

[liveinhope]

Youre welcome - just note it's not my code ..
the contextures site , by Debra Dalgliesh , is really good for learning more about pivot tables