Recorded macro changes Pivot table layout when replayed.

S Oberlander

Board Regular
Joined
Nov 25, 2020
Messages
153
Office Version
  1. 365
Platform
  1. 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 "
 

Attachments

  • Screenshot 1.png
    Screenshot 1.png
    6.5 KB · Views: 12
  • Screenshot 2.png
    Screenshot 2.png
    5.6 KB · Views: 12

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
You might have to record this to get the VBA:
In the PivotTable Options, the Layout & Format tab, make sure the "Preserve cell formatting on update" is checked and the "Autofit column widths on update" is unchecked.
 
Upvote 0
My problem is not with autofit
I want the MID, DBA, and Month filters should all be in column A stacked one on top of the other.
 
Upvote 0

Forum statistics

Threads
1,223,912
Messages
6,175,341
Members
452,638
Latest member
Oluwabukunmi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top