VBA - Change all pivot tables based upon pivot table 1 column lables

[Glen_Marshall]

Hi all,

I was provided with the very useful code below that sychs the cache of my pivot tables to that of pivot table1.

Sub SyncCaches()Dim MyPivot As PivotTable
Dim MySheet As Worksheet
     For Each MySheet In ActiveWorkbook.Worksheets
          For Each MyPivot In MySheet.PivotTables
               MyPivot.CacheIndex = Sheets("Pivot Count").PivotTables("PivotTable1").CacheIndex
          Next MyPivot
     Next MySheet
End Sub

I'm now after a way for all my pivot tables to have the same column lables as pivot table 1. Currently I have months from 2010 to 2014 however I need to alter that to only certain dates, therefore I woul dlike to change in on pivot table 1 and then hit some code to update the rest.

Is there a way the code can be adapted to achieve this? I'm assuming it's the CacheIndex that needs to change to something??

Many thanks

Glen

PS - using Excel 2010

 

[Jerry Sullivan]

Hi Glen, Since your PivotTables will share the same PivotCache and you are using xl2010 or later, have you considered using a slicer to sync the dates field?

 

[Glen_Marshall]

Morning Jerry,

I've tried to use the slicer function but it appears to have problems with the number of pivot tables I'm trying to link to (not sureif there is a limit or some issue as I use a a remote desktop connection permantely).

I've got quite a bit of VBA code now sat behind the sheet to control various aspects of my life(!!) / data therefore I'm happy to continue with this aproach, it also allows it to be backwards comaptiable (something I've found out the hard way before!!).

 

[Jerry Sullivan]

Glen, Sorry I didn't see your latest post until now.

To sync the Pivots using VBA instead of slicers, you could try the code in this thread (and the thread it references)....

http://www.mrexcel.com/forum/excel-questions/598984-pivot-table-question.html

The code is a few years old, and I'd probably code that a little differently these days, but hopefully it will serve your purpose.

 

[Glen_Marshall]

Hi Jerry,

Many thanks, apologies had a holiday so only just back on this!!

I'll have a play this weekend, all good fun this