Re-driecting a pivot quarterly

[Cass]

Folks,

I have 8 pivots set on one data set- on purpose, they are set up this way in
order to update a second excel data sheet for reporting. My pivots are set to
show 3 months of data- at least this is how I want it. I can refresh these
pivots no problem but I want the macro to re-direct the pivot every quarter.

I have is that i want to "hit a button!" and the marco will
1. recognise if there is a new quarter or not
2. If there is a new quarter to redirect the pivot to the new quarter raw
data sheet
3. If there is no new quarter sheet available to update then to just refresh
the pivot with the new months data.

Each quarter is held on a seperate sheet in a primary work book.

Is this at all possible or am I being wildly optimistic?! If this isn't
clear let me know and I will try to do a better job of explaining myself!

Thanks
Cass

 

[David]

I'm sure that some VB ace will tell you a different way but how I would do it.
Keep you four Qtr source sheets- create a new sheet to become the source of
the pivots
In this sheet us an If formula that reconnises the qtr you want to review-
maybe use a drop down on the first pivot sheet- this will then change the
source info- then simply refresh thye pivots- ahve them all linking off one
so to speed up the refresh

If the sheets are very large you could create 4 simple macros just to copy
the data from the qtr sheet to the pivot source sheet as well