Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot table page field (Excel 2003 sp2)

    I have 3 different pivot tables on a single worksheet that all have an employee name as the page field. I need to extract data for each employee from all 3 tables. I'm sure there's a better and easier way to do this, but this is what I've come up with so far.
    First I wanted to have each table display the same employee.
    ActiveSheet.PivotTables("FCR").PivotFields("Opened by").CurrentPage = _
    DWName
    ActiveSheet.PivotTables("FCRPass").PivotFields("Op ened by").CurrentPage = _
    DWName
    ActiveSheet.PivotTables("EVmail").PivotFields("Ope ned by").CurrentPage = _
    DWName
    The first 2 tables change the page field to the correct employee, but the 3rd one generates a run time error: Run-time error '1004' Unable to set the _Default property of the PivotItem class
    What am I missing? I verified the name of the table is EVmail.

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

    Re: Pivot table page field (Excel 2003 sp2)

    Without seeing the workbook, it's hard to say.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table page field (Excel 2003 sp2)

    I was able to pull out just a little bit of data so it would be small enough to attach here. The macro is not identical ( just listed one name instead of looping through everyone), but now I get a run time error on the first table. Hope this helps. I can't use just one table because my boss wants to be able to just open this file and see the data he wants quickly by just changing the name. I am pulling data for reports for all agents.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot table page field (Excel 2003 sp2)

    There is not Pivot table named "FCRPass" it is "PivotTable4"

    Why not try something like:
    <pre>Sub PVTTEst()
    Dim DWName
    Dim PT As PivotTable
    DWName = "Acuna, Francisco"
    For Each PT In Sheets("FCR").PivotTables
    PT.PivotFields("Opened by").CurrentPage = _
    DWName
    Next
    End Sub</pre>


    It will just loop thru all the privots on the sheet, no selecting the sheet and when you add more pivots, or rename them, it will still change all of them

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table page field (Excel 2003 sp2)

    I still get the same error.

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

    Re: Pivot table page field (Excel 2003 sp2)

    In the pivot table (and its source data) you attached, the name is not "Acuna, Francisco", but "Francisco ACUNA", so trying to set the page field to "Acuna, Francisco" causes an error. Macros can't handle inconsistencies, unless you tell them how to do that...

  7. #7
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table page field (Excel 2003 sp2)

    Sorry - since I was just copying some of the data and just that one peice of the macro, I didn't show that I do a conversion on the name. But even in the small file I sent, if I change the name to Francisco Acuna, I still get the same error.

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

    Re: Pivot table page field (Excel 2003 sp2)

    Have you tried Steve's macro? If I change "Acuna, Francisco" to "Francisco Acuna" in the code, it runs fine on the workbook you attached. Your original macro causes an error because it contains an incorrect pivot table name.

  9. #9
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table page field (Excel 2003 sp2)

    I had tried Steve's code, but still got the same error. I deleted the pivot table and recreated it, then ran just that piece of Steve's code against my original fine and it worked fine. Unfortunately when I put it into my original code I get the same error. Can someone maybe look at this and see what might be causing the error? I know the code is not clean. I'm fairly new at this, so I'm sure there's a conflict in there that I'm just not advanced enough to see. Please don't shudder when you look at this macro. I'll get better, but it's a slow process. Thanks so much for all the help and patience. You guys are the best!

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

    Re: Pivot table page field (Excel 2003 sp2)

    When the error occurs, you'll be given a choice between End, Debug and Help.
    Select Debug.
    The offending line will be highlighted.
    If you get an error in the For Each PT ... Next loop, the value of DWName must be invalid.
    Your code sets DWName to Worksheets("Background Metrics").Range("A1").
    While the code is paused, you can inspect the value of DWName by hovering the mouse pointer over the variable in the code. (See screenshot)
    Try to check whether DWName contains a name that does not occur in the pivot table.

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot table page field (Excel 2003 sp2)

    I don't get an error when I use a name that is not in the data table.

    I don't get an error with the sample workbook with my code...

    Steve

  12. #12
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table page field (Excel 2003 sp2)

    It is the For Each loop - and I verified the name value in DWName does appear in the pivot table. Now I really have no clue.

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

    Re: Pivot table page field (Excel 2003 sp2)

    Have you hidden some of the names? If one of the names has been hidden, you'll get an error too.
    Double click the gray 'Opened by' button in each of the pivot tables, and check carefully whether any of the items is selected in the list at the bottom of the Pivot Field dialog.

  14. #14
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table page field (Excel 2003 sp2)

    I did double check that no names were hidden.

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

    Re: Pivot table page field (Excel 2003 sp2)

    Both Steve and I have verified that the code he proposed works correctly on the sample workbook you provided. Could you attach a more representative sample, zipped if necessary?

Page 1 of 2 12 LastLast

Posting Permissions

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