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

    Code to select only body of pivot table, not labels

    Hello!

    I have the following bit of code that almost does what I need, but not quite... if anyone can tell me how to tweak it to have it just select the pivot table data highlighted in red in the pivot table example below and NOT to select the "row labels" and "values" label, it would be greatly appreciated.

    Thanks so much!
    Lana

    Code example:
    ActiveSheet.PivotTables("PivotTable1").TableRange1 .Select

    Pivot Table Example:

    Year Nr 2014
    Month Jan

    GL Amount
    Category Unit Cd Nature Cd Nature Name Total
    Net Sales 88PPERM 4000101 Intercompany Sales-GL $2,500
    Net Sales 88PWHEY 4000100 Intercompany Sales $3,500

  2. #2
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Try tablerange2

    ttp://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=154:r eferencing-an-excel-pivot-table-range-using-vba&catid=79&Itemid=475

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You could use:
    Code:
        With ActiveSheet.PivotTables(1).TableRange1
            .Resize(.Rows.Count - 1).Offset(1).Select
        End With
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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