Excel VBA Macro To Dynamically Change Source for Pivot Table

[Howardc]

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]

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)

[Admin]

http://www.excelfox.com/forum/f13/replace-pivot-table-source-range-new-address-vba-397/

[Howardc]

Thanks for the help, much appreciated