Macro to change PivotField selection (Excel 2002)

Topic

New Reply

Hi,
In my macro below I hard code the month and the macro works, however when I change the “JUN08” to reference Sheet2.Range(“A4”), it doesn’t work and I get a error that says “Unable to set the default property of the PivotItem class”. Any ideas why it isn’t working?
Thanks!
Lana

Sub PTMonth()

Sheets(“Dubuque Det”).Select
ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Period”).CurrentPage = _
“JUN08”

Sheets(“Sublette Det”).Select
ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Period”).CurrentPage = _
“JUN08”

End Sub

Reply | Quote

Replies

You must explicitly use the Value property of the range:

ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Period”).CurrentPage = Sheet2.Range(“A4”).Value

Otherwise Excel thinks you’re trying to assign a range object to a pivotitem object.

Reply | Quote

Yep… of course that worked! Thanks for the explanation Hans!
Lana

Reply | Quote