Multiple Pivot Tables - Single table of source data

some_evil

New Member
Joined
Feb 19, 2014
Messages
31
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...

MicrosoftExcel-401027_010000_022100csv_2014-03-10_11-41-35_zpsfda19d16.jpg


Thanks all.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

liveinhope

Well-known Member
Joined
Dec 16, 2013
Messages
857
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

New Member
Joined
Feb 19, 2014
Messages
31
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:

Code:
'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

Well-known Member
Joined
Dec 16, 2013
Messages
857
Youre welcome - just note it's not my code ..
the contextures site , by Debra Dalgliesh , is really good for learning more about pivot tables
 

Watch MrExcel Video

Forum statistics

Threads
1,123,369
Messages
5,601,223
Members
414,434
Latest member
Riyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top