Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Macro to change PivotField selection (Excel 2002)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to change PivotField selection (Excel 2002)

    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.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro to change PivotField selection (Excel 2002)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •