Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Apr 2004
    Location
    Grand Rapids, Michigan, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using GetPivotData in macro (Excel 2003 SP1)

    I have a workbook containing data tables that I run reports from using pivot tables. One of the tables lists names of people along with an "id number". I have written a macro that allows me to run a report for each persons id. However, some of these people have no data associated with them. I'd like to skip over them in my macro but I can't seem to figure out how I can eliminate them. I was thinking I could use the GetPivotData function and check that for greater than zero value. Unfortunately, I can't seem to get the function to work in a macro. Is there any way to use GetPivotData in a macro? Is there another function that would provide a similar outcome? Thanks for any help.

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

    Re: Using GetPivotData in macro (Excel 2003 SP1)

    You should be able to use something like

    If Range("D3").PivotTable.GetPivotData("Sum of Something", "ID number", 37, Other", "Test") > 0 Then

    This code tests if the value of the Sum of Something datafield is positive for ID number = 37 and Other = "Test"

  3. #3
    New Lounger
    Join Date
    Apr 2004
    Location
    Grand Rapids, Michigan, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using GetPivotData in macro (Excel 2003 SP1)

    Hans,

    Thanks for the help. I think I'm getting closer to what I need to do. Right now I have the following statement in my macro:

    If Range("A15").PivotTable.GetPivotData("Total") > 0 Then

    What I'm not sure on is what you were referencing with D3 in your example. I changed it to be A15 in mine since that cell contains the "Total" label in my pivot table that I want to check against. At least the macro is running now even if I'm still not getting the exact results I'm looking for...

    Thanks again.

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

    Re: Using GetPivotData in macro (Excel 2003 SP1)

    In fact, the cell you specify in Range(....) can be any cell in the pivot table. Range(...).PivotTable refers to the pivot table containing the upper-left corner of the specified range.

    You will probably want to specify the ID in the GetPivotData function.

Posting Permissions

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