Using Brian's nice code as a starting point and folding in your new info that some worksheets may have multiple pivot tables. (Your error was not specifying the collection
[PivotTables] in your For Each.)
Comments:
- No need to activate each sheet. Just make the changes and get on down the road.
- I used the active sheet as the "master" instead of keying off the ordinal position of a particular WS which would mean that if you rearrange the Worksheets then you [may] have to update the code.
- I assume the starting key as being in B1, not A1 since you indicated that you "wanted to change 1 and the rest follow suit". Note - as this stands you'd need to change one and then run this macro. However you could put this code on either:<ul>
- a specific WS that you want to use as a "master" and test for changes to target.address = "$B$1" or
- you could put it on the WB_SheetChange, same kind of test + test for existance of PT on WS and then go - this would mean you would have no "master" and a change to page (B1) month in any one PT automatically results in changes to all.
[/list]
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> SynchPivots()
<SPAN style="color:#00007F">Dim</SPAN> ThisMonth <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, pt <SPAN style="color:#00007F">As</SPAN> PivotTable
ThisMonth = ActiveSheet.Range("B1").Value
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> pt <SPAN style="color:#00007F">In</SPAN> ws.PivotTables
pt.PivotFields("Month").CurrentPage = ThisMonth
<SPAN style="color:#00007F">Next</SPAN> pt
<SPAN style="color:#00007F">Next</SPAN> ws
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>
{Edit}I just notice that Ragner was not the OP! VBean, you may or may not need to add the For Each PT loop (it shouldn't hurt anything to leave it in).{EndEdit}
HTH