Generic macro to generate report data in a pivot table with changing source data

Question

I generate multiple excel workbook scenarios all the time and need to create individual pivot tables to view the results (create graphs too). Surely there is a macro I can run to do this for me!

I tried recording the macro that I need (see below) but it only works when the source in the same and the new worksheet is the same.

how do I re-write my code to be... less specific so I can hit play on a new workbook as it comes in.

Here is my code so far:

Sub Output()
'
' Output Macro
'
' Keyboard Shortcut: Ctrl+r
'
    Cells.Select
    Sheets.Add
    ActiveWorkbook.Worksheets("Sheet5").PivotTables("PivotTable7").PivotCache. _
        CreatePivotTable TableDestination:="Sheet5!R3C1", TableName:="PivotTable7" _
        , DefaultVersion:=6
    Sheets("Sheet5").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable7")
        .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("PivotTable9").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable9").RepeatAllLabels xlRepeatLabels
    ActiveSheet.PivotTables("PivotTable9").AddDataField ActiveSheet.PivotTables( _
        "PivotTable9").PivotFields("Year"), "Sum of Year", xlSum
    ActiveSheet.PivotTables("PivotTable9").AddDataField ActiveSheet.PivotTables( _
        "PivotTable9").PivotFields("Data1"), "Sum of Data1", xlSum
    With ActiveSheet.PivotTables("PivotTable9").DataPivotField
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable9").AddDataField ActiveSheet.PivotTables( _
        "PivotTable9").PivotFields("Data2"), "Sum of Data2", xlSum
    ActiveSheet.PivotTables("PivotTable9").AddDataField ActiveSheet.PivotTables( _
        "PivotTable9").PivotFields("Data3"), "Sum of Data3", xlSum
    ActiveSheet.PivotTables("PivotTable9").AddDataField ActiveSheet.PivotTables( _
        "PivotTable9").PivotFields("Data4"), "Sum of Data4", xlSum
    ActiveSheet.PivotTables("PivotTable9").AddDataField ActiveSheet.PivotTables( _
        "PivotTable9").PivotFields("Data5"), "Sum of Data5", xlSum
    ActiveSheet.PivotTables("PivotTable9").AddDataField ActiveSheet.PivotTables( _
        "PivotTable9").PivotFields("Data6"), "Sum of Data6", xlSum
    ActiveSheet.PivotTables("PivotTable9").AddDataField ActiveSheet.PivotTables( _
        "PivotTable9").PivotFields("Total_Data7"), _
        "Sum of Total_Data7", xlSum
    ActiveSheet.PivotTables("PivotTable9").AddDataField ActiveSheet.PivotTables( _
        "PivotTable9").PivotFields("Data8"), "Sum of Data8", xlSum
    ActiveSheet.PivotTables("PivotTable9").AddDataField ActiveSheet.PivotTables( _
        "PivotTable9").PivotFields("Data9"), "Sum of Data9", _
        xlSum
    ActiveSheet.PivotTables("PivotTable9").AddDataField ActiveSheet.PivotTables( _
        "PivotTable9").PivotFields("Data10"), "Sum of Data10", _
        xlSum
    ActiveSheet.PivotTables("PivotTable9").AddDataField ActiveSheet.PivotTables( _
        "PivotTable9").PivotFields("Data11"), _
        "Sum of Data11", xlSum
    ActiveSheet.PivotTables("PivotTable9").AddDataField ActiveSheet.PivotTables( _
        "PivotTable9").PivotFields("Data12"), "Sum of Data12", xlSum
    ActiveSheet.PivotTables("PivotTable9").AddDataField ActiveSheet.PivotTables( _
        "PivotTable9").PivotFields("Data14"), "Sum of Data14", xlSum
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Sum of Year")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveWindow.SmallScroll Down:=-3
    Range("A20").Select
End Sub

See [How to avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — BruceWayne, May 26 '21 at 04:24
thanks for the info Bruce. rewriting the select out would be the start but i dont know how i get the pivot table part in... — Ez_newbie, May 26 '21 at 05:08
Also there are so many entries in that link BruceWayne - any in particular you are referencing? — Ez_newbie, May 26 '21 at 05:36

Generic macro to generate report data in a pivot table with changing source data

0 Answers0