Recorded macro changes Pivot table layout when replayed.

[S Oberlander]

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.

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 "

 

[johnny51981]

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.

 

[S Oberlander]

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.