How to change the sources of all Pivot tables in a workbook to a named data range?

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