PDA

View Full Version : [SOLVED] Pivot Table Grand Total



mdmackillop
05-20-2016, 08:49 AM
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
05-20-2016, 09:45 AM
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
05-21-2016, 02:23 AM
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