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

Glen_Marshall

New Member
Joined
May 6, 2014
Messages
9
Hi all,

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

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

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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?
 
Upvote 0
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!!).
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,224,209
Messages
6,177,151
Members
452,762
Latest member
manuha

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
Back
Top