Is there a way to use VBA or other solution to have my pivot tables point to the same file name and range, but in different folders?
For example, I have a folder named 2010-MAY. I have pivot tables that update from a file in that folder called Data.xls. The file has a text-import range called DataUpdate. The path to the dataset is '\\2010-MAY\Data'!DataUpdate. I would like to be able to copy the entire 2010-MAY folder, rename it 2010-JUNE, and somehow have the pivot table range location be updated automatically to '\\2010-JUNE\Data'!DataUpdate.
I think I'm looking for a way to set the range to a relative reference to '\\Data'!DataUpdate, perhaps using VBA.
Thanks!
For example, I have a folder named 2010-MAY. I have pivot tables that update from a file in that folder called Data.xls. The file has a text-import range called DataUpdate. The path to the dataset is '\\2010-MAY\Data'!DataUpdate. I would like to be able to copy the entire 2010-MAY folder, rename it 2010-JUNE, and somehow have the pivot table range location be updated automatically to '\\2010-JUNE\Data'!DataUpdate.
I think I'm looking for a way to set the range to a relative reference to '\\Data'!DataUpdate, perhaps using VBA.
Thanks!
