S Oberlander
Board Regular
- Joined
- Nov 25, 2020
- Messages
- 153
- Office Version
- 365
- Platform
- Windows
I recorded the steps of creating a new pivot table and formatting all necessary fields, I edited only the absolute reference to refer to variables instead. Yet when I run it, the layout of the table is not the same. See below code and screenshots. The first screenshot, is how I want it to be and how it came out when I recorded the macro, the second screenshot is the result from running the macro.
VBA Code:
Dim lastrow As Long
With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
Sheets.Add
ActiveSheet.NAME = "Pivot"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Details!R1C1:R" & lastrow & "C23", Version:=7).CreatePivotTable TableDestination:= _
"Pivot!R3C1", TableName:="Summary", DefaultVersion:=7
Cells(3, 1).Select
With ActiveSheet.PivotTables("Summary")
.ColumnGrand = True
.HasAutoFormat = True
.DisplayErrorString = False
.DisplayNullString = True
.EnableDrilldown = True
.ErrorString = ""
.MergeLabels = False
.NullString = ""
.PageFieldOrder = 2
.PageFieldWrapCount = 0
.PreserveFormatting = True
.RowGrand = True
.SaveData = True
.PrintTitles = False
.RepeatItemsOnEachPrintedPage = True
.TotalsAnnotation = False
.CompactRowIndent = 1
.InGridDropZones = False
.DisplayFieldCaptions = True
.DisplayMemberPropertyTooltips = False
.DisplayContextTooltips = True
.ShowDrillIndicators = True
.PrintDrillIndicators = False
.AllowMultipleFilters = False
.SortUsingCustomLists = True
.FieldListSortAscending = False
.ShowValuesRow = False
.CalculatedMembersInFilters = False
.RowAxisLayout xlCompactRow
End With
With ActiveSheet.PivotTables("Summary").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("Summary").RepeatAllLabels xlRepeatLabels
With ActiveSheet.PivotTables("Summary").PivotFields("MID")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("Summary").PivotFields("DBA")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("Summary").PivotFields("Month")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("Summary").PivotFields("Category")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("Summary").AddDataField ActiveSheet.PivotTables( _
"Summary").PivotFields("VOLUME"), "Sum of VOLUME", xlSum
ActiveSheet.PivotTables("Summary").AddDataField ActiveSheet.PivotTables( _
"Summary").PivotFields("COUNT"), "Sum of COUNT", xlSum
ActiveSheet.PivotTables("Summary").AddDataField ActiveSheet.PivotTables( _
"Summary").PivotFields("RATE"), "Sum of RATE", xlSum
ActiveSheet.PivotTables("Summary").AddDataField ActiveSheet.PivotTables( _
"Summary").PivotFields("PI RATE"), "Sum of PI RATE", xlSum
ActiveSheet.PivotTables("Summary").AddDataField ActiveSheet.PivotTables( _
"Summary").PivotFields("Fee on STMT"), "Sum of Fee on STMT", xlSum
ActiveSheet.PivotTables("Summary").AddDataField ActiveSheet.PivotTables( _
"Summary").PivotFields("Returned fee"), "Sum of Returned fee", xlSum
ActiveSheet.PivotTables("Summary").AddDataField ActiveSheet.PivotTables( _
"Summary").PivotFields("Chargeback"), "Sum of Chargeback", xlSum
With ActiveSheet.PivotTables("Summary").PivotFields("Description")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("Summary").PivotFields("Sum of VOLUME")
.Caption = "VOLUME "
.NumberFormat = "$#,##0.00"
End With
ActiveSheet.PivotTables("Summary").PivotFields("Sum of COUNT").Caption = _
"COUNT "
With ActiveSheet.PivotTables("Summary").PivotFields("Sum of RATE")
.Caption = "Average of RATE"
.Function = xlAverage
End With
ActiveSheet.PivotTables("Summary").PivotFields("Average of RATE").Caption _
= "RATE "
With ActiveSheet.PivotTables("Summary").PivotFields("Sum of PI RATE")
.Caption = "PI RATE "
.Function = xlAverage
End With
With ActiveSheet.PivotTables("Summary").PivotFields("Sum of Fee on STMT")
.Caption = "Fee on STMT "
.NumberFormat = "$#,##0.00"
End With
With ActiveSheet.PivotTables("Summary").PivotFields("Sum of Returned fee")
.Caption = "Returned fee "
.NumberFormat = "$#,##0.00"
End With
ActiveSheet.PivotTables("Summary").PivotFields("Sum of Chargeback"). _
Caption = "Chargeback "