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
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