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

    VBA for Pivot Table Current Page (Excel 2002)

    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

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

    Re: VBA for Pivot Table Current Page (Excel 2002)

    Try

    ... = Worksheets("Sheet2").Range("A4")

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

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

    Re: VBA for Pivot Table Current Page (Excel 2002)

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

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

    Re: VBA for Pivot Table Current Page (Excel 2002)

    Try this:

    ActiveSheet.PivotTables(1).PivotFields("Period").C urrentPage = _
    Worksheets("Select Period").Range("A4").Value

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

    Re: VBA for Pivot Table Current Page (Excel 2002)

    This worked... thanks 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
  •