Syncronizing 2 or more Pivot Tables

[vbean]

I have several (30+) pivot tables used to generate monthly reports.

I have automated the production of these reports except each month I must go into each pivot table and select the next month.

Is there any way to link or syncronize these pivot tables so I only need to change the month of interest in one place and all of the pivot tables will be updated?

Thanks for your help

 

[Greg Truby]

vbean,

In my test WB, I had the months set up as strings. I went ahead and made 'em dates and the code still ran without any hiccups. However, to really be on the safe side, you could go ahead and change the DIM statement to read:
Dim ThisMonth As Variant, pt As PivotTable
I did run into the formatting wierdness you describe. If I went to each PT and selected cell that was the Page variable (B1 in most of the PT) and formatted the cell (Ctrl+1) as mmm-yy then the formatting seemed to stick. You did say one thing that concerns me... You said that these are actual datevalues but in your example you had a decimal portion (time component). If that's the case, then is your month a page field? I ask because I get a unique page value for each item, i.e. 37987.0, 37987.25, 37987.5, 37987.75 (Jan 1, 2004 at 0:00, 6:00, 12:00 and 18:00) each show up as possible pages and I can't get XL2002 to group a page field by day or month or year like you can with row or column fields.

 

[vbean]

Greg,
Yes my month is in the "Page Field" section of the pivot table.

I tried the Dim ThisMonth As Variant, pt As PivotTable and got the same wierdness in the formating, I am hoping that my use of the Page field makes a solution obvious to you.

For further background my month field is calculated as follows
=DATE(YEAR(J203),MONTH(J203),1) for each record in the raw data. So each row for a given month has the exact same value in the Month field.


thanks
Vern

 

[Greg Truby]

vbean,

Well, this beats the heck outta me! (Not what you wanted to hear, I'm sure)

Try the following code (suggest using a copy WB, not your original): I tried setting the number format two ways and neither sticks. You'd think at least one of them would permanently set the number format. But on mine, some of the cells are stubbornly clinging to another format. What's even more fun? Try running this a few times for various months. You'll notice that it appears that it's not the cell itself that's holding the color formatting. New months won't be blue until you've run this and made them blue. Then change the color constant in the code and paint a couple of 'em vbRed. Then go back and by hand switch to a month you'd painted blue but not red. The color flips back to blue, the red doesn't seem to be "attached" to the cell itself, but rather to a particular value on the list. Maybe one of the MVP's around here can tell us what the heck is goin' on, 'cause at least what's happening on my system ain't makin' much sense to me.

<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">Variant</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
            pt.PivotFields("Month").NumberFormat = "mmm-yy"
            pt.PivotFields("Month").DataRange.NumberFormat = "mmm-yy"
            <SPAN style="color:#007F00">'Debug.Print pt.PivotFields("Month").DataRange.Address(False, False, xlA1, True)</SPAN>
            pt.PivotFields("Month").DataRange.Interior.Color = vbGreen
        <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>

 

[vbean]

Greg,
I can confirm I am getting the same results you describe.

Can anyone shed some light on this.
Are the formats attached to a particular value on the list and not the cell? And If so, can we access the values on the list to use to synch multiple pivot tables (see original question)

Thanks
vbean
---------------------
Excel2k SP3 on Win2k

 

[vbean]

Can anyone shed some light on this.
Are the formats attached to a particular value on the Pivot Table list and not the cell? And If so, can we access the values on the list to use to synch multiple pivot tables (see original question)

 

[BrianB]

I think you first need to go back to your pivot table and check that it is working without using macros.

I guess that part of your problem is that VBA always does its damdest to change dates to the American "mm/dd/yy". Another part is that even when using months you need to ensure that the *day* is correct, because your pivot table will discriminate - even though the worksheet format might show it as the same.

Here is a code snippet to show the principle I use to format my 'Month' column with a date like "Feb-04". I have to ensure that I explicitly convert it with day 1 otherwise, as mentioned, VBA tries to do something else. The act of using "Feb" rather than number 2 for the month helps too. I then convert the string to an explicit DateValue for that day.

'-----------------------------------
Sub month_to_column()
'- month & date from sheet
MonthName3 = "Feb"
JournalYear = "04"
ThisMonth = "1-" & MonthName3 & "-" & JournalYear
'- loop
For rw = 1 To 100
ActiveSheet.Cells(rw, 1).NumberFormat = "mmm-yy"
ActiveSheet.Cells(rw, 1).Value = DateValue(ThisMonth)
Next
End Sub
'-----------------------------------------