Friday, 5 September 2014

How to Set the Pivot Table Grand Totals on for Rows and Columns in Excel

Excel VBA Macro to Set the Pivot Table Grand Totals on Rows and Columns
Sub PVT_GrandTotals_OnRows_Columns ()
Dim Pivot_Name as String
'Select any in Cell the Pivot Table Area
ActiveSheet.Range("I4").Select 
'Storing Pivot Table Name in a Variable
Pivot_Name = Selection.PivotTable.Name

    With ActiveSheet.PivotTables(Pivot_Name)
        .ColumnGrand = True
        .RowGrand = True
    End With

End Sub

Eg :
Pivot Table without Grand Totals :

Pivot Table with Grand Totals on Rows and Columns:

Manually we can Set the Pivot Table Grand Totals on Rows and Columns by selecting "PivotTable Tools>Design>Grand Totals>On for Rows and Columns" as shown below.



By doing this we will get "Grand Total" for Columns at Last Row and for Rows at Last Column of the Pivot Table.

Thanks,
TAMATAM






1 comment:

  1. Hi Users ,
    Setting the Pivot Table Grand Totals on Rows and Columns is very important when you using the following lines of code in your project , other wise we will get a run time error while assigning the ShowDetails Object(Eg: Set PVT_GrandTotal ):

    Set PVT_GrandTotal = ActiveSheet.Range("C7").PivotTable.GetPivotData("Sum of Value")
    PVT_GrandTotal.ShowDetail = True

    ReplyDelete

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts