Pivot Table Grand Total

[mdmackillop]

I'm creating a pivot table using the following code, but am not getting a Grand Total column. Any suggestions?
Regards
MD

Sub CostxPivotZones()
    Dim r As Range
    Dim Head As Range
    Dim sh As Worksheet
    Dim i As Long
    Dim Hd As String


    'Clear old data
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Pivot").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True


    Set r = Cells(1, 1).CurrentRegion
    'Get column headers
    Set Head = Range(Cells(1, 7), Cells(1, Columns.Count).End(xlToLeft))
    Set sh = Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
                                      r, Version:=xlPivotTableVersion15). _
                                      CreatePivotTable TableDestination:=sh.Cells(3, 1), TableName:="PivotTable1" _
                                    , DefaultVersion:=xlPivotTableVersion15




    With sh.PivotTables("PivotTable1")
        'Set row fields
        With .PivotFields("Zone")
            .Orientation = xlRowField
            .Position = 1
        End With
        With .PivotFields("Type")
            .Orientation = xlRowField
            .Position = 2
        End With
        'Set Column Fields
        For i = 1 To Head.Cells.Count
            Hd = Head(i).Value
            .AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields(Hd), " " & Hd, xlSum
        Next i
        
        'Suggested solution but no result
        .ColumnGrand = True
        .RowGrand = True
        '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
        
        
        .TableStyle2 = "PivotStyleLight14"
        .ShowTableStyleColumnStripes = True
    End With
    Cells.NumberFormat = "0.00"
    Rows(3).HorizontalAlignment = xlRight
    Columns("B:Z").Columns.AutoFit
    Application.Goto sh.Cells(3, 1)
    ActiveSheet.Name = "Pivot"


End Sub

[Paul_Hossler]

Your G-Z are not really column headers, essentially the same as more row headers, so there was nothing to generate a column GT

Making your data into a list and making a PT manually seems to give the format that I think you were expecting

[mdmackillop]

Thanks Paul
It greatly simplifies the coding putting the Height categories into the table. The Headers are not required and the Pivot works correctly.
Regards
Malcolm