Multiple Pivot Tables - Single table of source data

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...

Microsoft Excel - 401027_0100.00_0221.00.csv_2014-03-10_11-41-35.jpg

Thanks all.

I have been given the solution!!! I just created a simple macro button, and now when I create my second Pivot Table I select somewhere within then run macro. This code opens a new sheet, creates a pivot table (and a new cache), then it assigns the new cache to the selected pivot, then deletes the new sheet and then the two pivot tables work independently!

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

Please Login or Register  to view this content.