VBA for Pivot Table Current Page (Excel 2002)

Topic

New Reply

Hi,
I recorded the following macro:

Sub PTMonth()
Sheets(“Dubuque Det”).Select
ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Period”).CurrentPage = “MAR08”
End Sub

I tried to change the = portion to refer to a different worksheet (see code below) and it just won’t work. I get a “Unable to set the _Default property of the PivotItem class” run-time error 1004 message. Any ideas why it’s not working… I swear I’ve done this before and it worked???
Thanks as always!!
Lana

Sub PTMonth()
Sheets(“Dubuque Det”).Select
ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Period”).CurrentPage = Sheet2.Range(“A4”)
End Sub

Reply | Quote

Replies

Try

… = Worksheets(“Sheet2”).Range(“A4”)

If that doesn’t work, could you attach a sanitized and if necessary zipped version of your workbook?

Reply | Quote

I tried the change and I still couldn’t get it to work… I’ve attached a small version.
Thanks!!
Lana

Reply | Quote

Try this:

ActiveSheet.PivotTables(1).PivotFields(“Period”).CurrentPage = _
Worksheets(“Select Period”).Range(“A4”).Value

Reply | Quote

This worked… thanks Hans!
Lana

Reply | Quote