As the heading suggests this is what I'm attempting to do, I have about 30-40 pivot tables, all PT's (save 3) share the same data source I had them coded with specific ranges, but now I'm trying to make the range dynamic, accounting for future expansion of the data set (when more rows are added).

My current VBA code is below - the line in bold is where the code breaks with the error mentioned. Any ideas why?

Sub update_source()
Dim wb As Workbook
Dim PC As PivotCache
Dim ws As Worksheet
Dim rng As Range

openFile (RPL) ' This is calling another subroutine to open the files, works

Set wb = ActiveWorkbook

Sheets("Data").Select
Set rng = Range("A1")
Set rng = Range(rng, rng.End(xlDown))
Set rng = Range(rng, rng.End(xlToRight))
wb.Names.Add Name:="Data1", RefersTo:=rng

Sheets("Avail Data").Select
Set rng = Range("A1")
Set rng = Range(rng, rng.End(xlDown))
Set rng = Range(rng, rng.End(xlToRight))
wb.Names.Add Name:="Data2", RefersTo:=rng

For Each PC In wb.PivotCaches
  PC.SourceData = "=Data1" 
Next PC

End Sub
PC.SourceData = "=Data1"- this is where the code breaks, with run time error 1004: Application defined or Object defined error.

I have tried to refer to the named range as both this formula and also as Range("Data1") - both does not work.

"Data1" as you can see is defined above, I have tried defining the ranges before running this code to see if that fixes it. But no luck.

All help is welcome, I have no idea why this is happenign.

Thank You - Abhi