VBA Express : Excel - Find and Expand Grand Total in PivotTable

Excel

Find and Expand Grand Total in PivotTable

Ease of Use

Easy

Version tested with

2003

Submitted by:

CatDaddy

Description:

This searches for the last cell in a PivotTable, the "Grand Total", then shows the source data in a new sheet.

Discussion:

This can be used when you recieve a pivot table but you need to work with all of the original data, x and y could be manually set to a static number if a different portion of the PivotTable.

Code:

instructions for use

			Sub Get_GTotalPT() 
     
    Dim rngPTTot As Range, rngPTData As Range 
    Dim x As Long, y As Integer 
     
    With ActiveSheet 
        Set rngPTData = .PivotTables(1).DataBodyRange 
        x = rngPTData.Rows.count - 1 
        y = rngPTData.Columns.count - 1 
         
        Set rngPTTot = rngPTData.Cells(1).Offset(x , y) 
        rngPTTot.ShowDetail = True 
    End With 
     
     
End Sub

How to use:

Open Workbook containing PivotTable
Open Developer Tab
Select Visual Basic
Right click on project and add module
Paste code into module
Save
Close Visual Basic Editor
To run, click macros under developer tab and run "Get_GTotalPT"

Test the code:

Sample File:

No Attachment

Approved by Jacob Hilderbrand

This entry has been viewed 12 times.