Excel VBA Macro To Dynamically Change Source for Pivot Table

**Howardc** · 03-04-2013, 03:24 PM

I have a spreadheet with a sheet named "Pivot table" and a sheet named "Import" Data"

I use a macro to import the data and to total and age the data on the sheet "Import Data" The macro works perfectly

I need a macro that will change the Pivot Table Data Size from A1 to where the last value appears in Column L. However, text "Total WIP" appears in Col G three rows after the last value in Col G and the total value appears three columns to the right of this

I have attached a sample

Your assistance in this regard is most appreciated

**Excel Fox** · 03-04-2013, 11:19 PM

Code:

    Worksheets("Pivot Table").PivotTables("PivotTable4").ChangePivotCache _
    ActiveWorkbook.PivotCaches.Create _
        (SourceType:=xlDatabase, SourceData:=Worksheets("Imported Data").Range("A1:L" & _
        Worksheets("Imported Data").Cells(Rows.Count, 1).End(xlUp).Row).Address(External:=True), _
        Version:=xlPivotTableVersion14)