Pivot Chart Filter Boxes Sticking to Row A?

[tezza]

Hi All,

When I create a pivot chart and add items into the 'Filter' box, the items go down the sheet - which is great.

When I did a macro record and played it back the items in the 'Filter' box go across Row A which isn't great as I want to adjust the column widths.

How do I change that in the code?

Feel free to shorten it as I know the macro adds a load of stuff not really needed and I don't know what to remove.

Sub Quarterly_Pivot_Chart()
'
' Quarterly_Pivot_Chart Macro
'

'
    Sheets("Sheet1").Select
    Sheets.Add(After:=ActiveSheet).Name = "Qrtly"
    Sheets("Qrtly").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R974C25", Version:=8).CreatePivotTable TableDestination:= _
        "Qrtly!R3C1", TableName:="PivotTable2", DefaultVersion:=8

    With ActiveSheet.PivotTables("PivotTable2")
        .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("PivotTable2").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable2").RepeatAllLabels xlRepeatLabels
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Chargeable Rate Sheet" _
        )
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Staff Staff Pay Group" _
        )
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields( _
        "Staff First Name Staff Last Name")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Product Code")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Call Duration"), "Sum of Call Duration", xlSum
    Range("C4").Select
    Range(Selection, Selection.End(xlToRight)).Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 90
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("C1").Select

End Sub

 

[tezza]

It's ok, I've found it - Pivot table settings changed for some reason to over and down instead of down and over.