update multiple pivot table filters containing hierarchy's based on date

[rjperket]

Any help on this is much appreciated!!!!

I'll do my best to explain what I am looking for,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have an excel book we use to run a quarterly report which has one worksheet for each of the company's departments<o></o>
Each of those worksheets has 3 pivot tables for the 3 months in the quarter.<o></o>
What I want to do is change the filters in the pivot tables based on the userform's change in date automatically using a macro.<o></o>
<o></o>
The only thing that really changes in this report is the date...<o></o>
<o></o>
One of the filters is simply a date filter and this selection will be the same in all of the 14 department sheets. <o></o>
In other words, if the first pivot in Sheet1 is filtered for Jan, then all of the first pivots in the sheets2-14 would also be Jan.<o></o>
As such, the second pivots would all be Feb and third pivots Mar.<o></o>
<o></o>
The curveball...<o></o>
<o></o>
The other filter is much more complicated. The way the data is exported from our database is as a hierarchy, this means that the filter has subsets that go down the tree by Division then Dept then Date then Product Class.<o></o>
<o></o>
No matter the date, the first sheet will always have the same division and department. The product class will also always be the same but, since it is a subset of the "Date" hierarchy, it changes in each new report.<o></o>

For example the first tab will always be:<o></o>

• Div CB<o></o>
  • <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:address w:st="on"><st1:Street w:st="on">Dept</st1:Street> 1</st1:address><o></o>
    • Date "Month 1"<o></o>
      • Classes 102, 104, 105...ect<o></o>
• Div CB<o></o>
  • <st1:address w:st="on"><st1:Street w:st="on">Dept</st1:Street> 1</st1:address><o></o>
    • Date "Month 2"<o></o>
      • Classes 102, 104, 105...ect<o></o>
• Div CB<o></o>
  • <st1:address w:st="on"><st1:Street w:st="on">Dept</st1:Street> 1</st1:address><o></o>
    • Date "Month 3"<o></o>
      • Classes 102, 104, 105...ect<o></o>

Tab 2 will be as such:<o></o>

• Div CB<o></o>
  • <st1:address w:st="on"><st1:Street w:st="on">Dept</st1:Street> 2</st1:address><o></o>
    • Date "Month 1"<o></o>
      • Classes 280, 282...ect<o></o>
• Div CB<o></o>
  • <st1:address w:st="on"><st1:Street w:st="on">Dept</st1:Street> 2</st1:address><o></o>
    • Date "Month 2"<o></o>
      • Classes 280, 282...ect<o></o>

<o></o>
Ideally, I would like to be able to select a single month and have the pivot tables update all three months on all of the worksheets. I would also like to be able to have it recognize the values that it needs to change in the filter that contains the hierarchy.

Thank you for your help!

Ryan

 

[rjperket]

P.s. Forgot to include that I am using MS Excel 2003 and the data is coming from an OLAP and is querried automatically when the pivot tables update.

Thanks again!