Get grand total from pivot table

[karimhemani]

Hello All,

Thank you for your help in the past. I'm here with another question.

I need code to get the grand total of the row totals in a pivot table. Using
this grand total, I would need to delete the sheet (each sheet has only one
pivot table) if the grand total is zero.

I don't know what the name of the pivot table is, but I know for sure each
sheet that the macro is run on will have only one pivot table. Each pivot
table does have a row and column titled "Grand Total" and the grand total
that I need is the summation of the totals of each individual row. The pivot
table does have this data displayed on most bottom right.

Any help would be greatly appreciated.

-Karim

 

[Bernie Deitrick]

Karim,

Try the macro below.

HTH,
Bernie
MS Excel MVP


Sub Macro1()

Dim mySht As Worksheet

On Error GoTo NoPT
With Application
.EnableEvents = False
.DisplayAlerts = False
End With

For Each mySht In ActiveWorkbook.Worksheets
mySht.Select
mySht.PivotTables(1).PivotSelect "", xlDataAndLabel, True
If Selection.Cells(Selection.Cells.Count).Value = 45 Then
mySht.Delete
End If

NextSht:
Next mySht

With Application
.EnableEvents = True
.DisplayAlerts = True
End With

Exit Sub

NoPT:
Resume NextSht
End Sub

 

[Bernie Deitrick]

Sorry, the 45 should be 0

If Selection.Cells(Selection.Cells.Count).Value = 45 Then
mySht.Delete
End If

Should have been

If Selection.Cells(Selection.Cells.Count).Value = 0 Then
mySht.Delete
End If

My test Pivot Table had 45 as the Grand Total.....

HTH,
Bernie
MS Excel MVP