Changing source data on a Pivot table by VBA

Hi,

I am trying to change the source data of some pivot tables using VBA. I have 22 Data sheets and each is in the same format, I want my pivot tables on my pivot table sheet "Dash 1" to change their source data based on a cell value (I.e."G38" shows Data 1, Data 2.... and I want to be the Sheet name that the source data is changed to).
My Data Sheets are called Data 1: Data 22 and the name of the Sheet I want to change the source data to is in a cell in my sheet with the pivot tables.
The source data is in columns A:AC rows 2 to 10,000

So if G38= "Data 1" then the pivot tables in "Dash 1" show the data from "Data 1"...

Hope you can help

Hi Olly,

Thanks for the help but I only want the pivot tables in sheet "Dash1" to update not all of the pivot tables in my workbook, any chance you could change the code for me?

Cheers

Thank you Olly can I contact you tomorrow regarding the above as I am going to be out of the office for the rest of the day?

Hi Olly,

I have managed to get it working using this:

Please Login or Register  to view this content.

There is probably a nicer way of doing it but it seems to work now.

Thanks for your help