• Macro to change PivotField selection (Excel 2002)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Macro to change PivotField selection (Excel 2002)

    Author
    Topic
    #453213

    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

    Viewing 0 reply threads
    Author
    Replies
    • #1121553

      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.

      • #1121554

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

    Viewing 0 reply threads
    Reply To: Macro to change PivotField selection (Excel 2002)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: