VBA Pivot Table to Change and Look at Data Source based on Cell Value

[TTUK]

Hi,

I really need some help in creating a macro whereby a Pivot Table's source is automatically changed and updated based on a cell value....


I have a Pivot Table which is in a Workbook Sheet Name of 'Stock VLookup' and in Cell D5 I have the Pivot Table.

I want this Pivot Table to pull through its source location which on our server is
S:\05 Supply Chain\PD\

The file name: Hourly stock 140316.xls

So the complete file path for the spreadsheet to lookup is: S:\05 Supply Chain\PD\140316.xls



So effectively in Cell D4 on the Stock Vlookup tab, id like the user to be able to type in the file name of 140316.xls and the data pull through in the Pivot Table.


Another way of me trying to explain this is:

1. Sheet Name Stock VLookup Has Pivot Table located in cell D5
2. In cell D4 is the filename of the file i need the pivot table to source to change too
3. Pivot Table to change is source in the filepath location of S:\05 Supply Chain\PD\ to lookup the file name is D4
4. Data in the Pivot Table is updated.


I do hope this makes sense and someone much clever than me can help me out with this?!

I look forward to your support!