Hi there!

Wondering if anyone can help me with this problem. I am fairly new to
macros and haven't even come across pivot tables until now, so any
help would be appreciated here.

What I have is a spreadsheet which links into an OLAP cube to run
filtered reports, there is a date drop down list which is the main
filter, this goes like this...

Time By Week
|
+-Year
+------Week
+-----------Day
+---------------Shift

This is filtered usually by week (we don't drill down as low as day or
shift, but the option is there should anyone need it). Now, the
problem is, that every week the managers link to this sheet via the
intranet to see the latest figures, and someone has the unthankful
task of opening 100 spreadsheets, advancing to the next week, then
resaving the sheet, to save the managers some time! Now, I have worked
out a VBS script which can open all these sheets, run a macro and then
close the sheets, and I can schedule this script weekly, what I don't
know is how to , in VBA, change the time by week drop down list to the
next week. I have tried recording a macro and manually changing it,
but this just shows me how to set it to a known value. Is there a way
i can dynamically change it to just the next record? For what its
worth, here is what I get when I manually change it to the next record
in Macro Recorder....




ActiveSheet.PivotTables("PivotTable2").PivotFields ("[Time By Week]").
_
CurrentPageName = "[Time By Week].[All Time By
Week].[2005].[Week 6]"


Anyone able to help? Thanks!!!!!!

num = 6
ActiveSheet.PivotTables("PivotTable2"). _
PivotFields("[Time By Week]"). _
CurrentPageName = _
"[Time By Week].[All Time By Week].[2005].[Week " & _
num & "]"

Would be my guess. Not sure how the two digit weeks look, but if they are

Week 11
then it shouldn't be a problem.

If they are Week11 then you would need a slight modification.

num = 6
snum = Right(" " & num,2)
ActiveSheet.PivotTables("PivotTable2"). _
PivotFields("[Time By Week]"). _
CurrentPageName = _
"[Time By Week].[All Time By Week].[2005].[Week" & _
snum & "]"




--
Regards,
Tom Ogilvy

"Keilan Knight" wrote in message
om...
Hi there!

Wondering if anyone can help me with this problem. I am fairly new to
macros and haven't even come across pivot tables until now, so any
help would be appreciated here.

What I have is a spreadsheet which links into an OLAP cube to run
filtered reports, there is a date drop down list which is the main
filter, this goes like this...

Time By Week
|
+-Year
+------Week
+-----------Day
+---------------Shift

This is filtered usually by week (we don't drill down as low as day or
shift, but the option is there should anyone need it). Now, the
problem is, that every week the managers link to this sheet via the
intranet to see the latest figures, and someone has the unthankful
task of opening 100 spreadsheets, advancing to the next week, then
resaving the sheet, to save the managers some time! Now, I have worked
out a VBS script which can open all these sheets, run a macro and then
close the sheets, and I can schedule this script weekly, what I don't
know is how to , in VBA, change the time by week drop down list to the
next week. I have tried recording a macro and manually changing it,
but this just shows me how to set it to a known value. Is there a way
i can dynamically change it to just the next record? For what its
worth, here is what I get when I manually change it to the next record
in Macro Recorder....




ActiveSheet.PivotTables("PivotTable2").PivotFields ("[Time By Week]").
_
CurrentPageName = "[Time By Week].[All Time By
Week].[2005].[Week 6]"


Anyone able to help? Thanks!!!!!!

Thanks for the reply Tom!

I think the best thing I can take out of that is probably the simplest
thing, escaping to a variable within the page name using & var & , I
haven't used this language so did not know how to include variables
(had tried all variations of methods I knew! :)

What I've done is used primitive excel macros and C&P the drop down
field into another cell, which copies it as just a general cell, I've
then copied the cell to the cell below, which has incremented it's
value from Week 1 to Week 2 , I then create a variable varWeek from
that new cell, and set the current page name to

CurrentPageName = "[Time By Week].[All Time By Week].[2005]. _
["& varWeek & "]"

Works a treat! Thankfully this works well too...

IF varWeek "Week 52"
THEN varWeek = "Week 1"
ENDIF

I've also replace the year with a variable, with I get with the
following

varYear = Trim(VBA.Format(Now(), "YYYY"))

Hoepfully this means, when the macro runs in the 1st week of 2006, it
will select 2006 as the year and reset Week to Week 1. Thanks for
helping me in simplifying this for me!!

Regards,
Keilan