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!!!!!!