MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Pivot tables


Posted by jeff on January 26, 2002 1:50 PM

I have a worksheet that has multiple pivot tables. All of which are driven by day of the month. They all come from the same data table but each pivot table represents a different reporting item.

Therefore when the data table gets updated and I change the page header for the day of the month (say the 5th), how can I get all of the page headers for all of the pivot tables to refresh their data and change their page header to match the "lead" pivot table? Any ideas would be appreciated.


Posted by Chris D on January 27, 2002 2:58 AM

Hi Jeff,

had a fiddle around, but the page headers won't allow formulae (ie for linking) or range names (ditto) for it's criteria, otherwise it would be simple

Unless anyone has a better idea, you may have to invest a few minutes recording (or writing) 31 individual macros that just update that page header to a certain date on each pivot one by one

(maybe record the first as changing the date to 1st of the month then save and just copy the VBA code another 30 times, changing 1st to 2nd, 3rd, 4th etc etc and saving each new version as it's own macro)

an hour's work max..... but then they are all there for ever

Sorry I can't be of much more use
Chris