PivotTable VBA Error

[BILLONEILL40]

I have a set of data that I replace each week. I have found that a pivot table I created last week will lose its links when I replace the data on sheet "Working Data". Is it possible to keep the range A:L in a Pivot Table, along with the format of what data is in rows and such?

If the above isn't possible, I have moved onto creating a macro to recreate each PivotTable each week, the issue with this is I run into this error: "unable to get the pivot tables property of the worksheet class", I believe it is because the .PivotTables("PiviotTable14") is the issue?

ActiveWorkbook.Worksheets("Summary").PivotTables("PivotTable14").PivotCache. _
CreatePivotTable TableDestination:="PivotTable!R2C1", TableName:= _
"PivotTable16", DefaultVersion:=6

Any insight would be appreciated...

[dflak]

Consider building your Pivot Table from an Excel Table. http://www.utteraccess.com/wiki/Tables_in_Excel.

Excel tables know how big they are, so if you remove the old data and copy / paste in the new data and refresh the pivot table, you should be good.