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?

Please Login or Register  to view this content.
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