VBA to update file path for pivot

[Lefemmenikita]

Hi

I was wanting a vba code to update the data source for a pivot in a file so that it points to the current month.


E.g, I have a file called 'Manufacturing Month end May 2017'. This file is saved under C:/Accounts/May 17.


There is a pivot in that file pointing to[C:/Accounts/May 17/Raw data.xls]sheet1!A:Z

Now, when I save that file into the Jun folder, I want the pivot to now point to
[C:/Accounts/Jun 17/Raw data.xls]sheet1!A:Z



The 'Manufacturing Month end Jun 2017' report file will always be saved in the same network folder as the raw data



Thanks

 

[Joe4]

Cross-posted here: VBA to update file path for pivot

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Lefemmenikita]

Apologies for that. I will only post on one forum in future

 

[Joe4]

I will only post on one forum in future

If you try posting it somewhere else first, and don't get a resolution, we do not have a problem with your posting it here also. We just ask that you mention that you did so and provide a link to it.
That way, others can see what was already done and don't waste or duplicate efforts.

Thanks.

 

[Lefemmenikita]

Bumping up this thread and adding one amendment to the code requirement:


Is there a VBA code which allows you to link a pivot to an external data source and is pointing to a specific tab in the external data source?

e.g. Manufacturing Jun 17 month end workbook contains a pivot that points to the tab called 'Manufacturing' in a workbook called Raw data.xlsx.
The Raw data file is saved in the same folder