Controlling Pivot Function from Form Combo Box

[JamieLight]

I am looking to control a pivot table option from a drop down menu on another worksheet. I have one page field on the pivot table which is 'Week01' to 'Week52' which controls all the options in the main doy of the pivot table. I have set up a drop down menu on another worksheet, which contains the list of weeks 01 to 52, but I cannot control the pivot field....

A friend has provided me with this code...



Public Sub sb_ChangePvt(strWeek As String)

' to use this function use the following code on the update of a cell
' or dropdown box
' call sb_ChangePvt("Week1") will change the pivot to week 1

Sheets("Pivot>>>>").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Week No").CurrentPage = strWeek

End Sub

I cant seem to get this to work, any ideas?

Thanks