Changing Pivot Data Source to another Tab in same workbook using VBA

[Siddharth Sarvoday]

Hi Team,

Can any one help me in here with syntax please !!
i have one Tab (Latest Pivot Tab) with Pivots and for it the Data is in another Tab ex:- (1st Month Tab) and for next month, i just have to update the Data in another Tab (2nd Month Tab)...
Here the Same Pivots in the (Latest Pivot Tab) needs to be updated/refreshed only with the (2nd Month Tab).

Appreciate your help here.. Thank you-

 

[Siddharth Sarvoday]

Anybody please help here.. ???

 

[Anthony47]

I think you can get the code for that operation by recording a macro while manually executing the commands:
-start Recording New Macro
-select the pivot table
-from PivotTable Tools /Options, press "Change source data"
-select the new source datas and Confirm
-right click on the table and select Update
-Stop recording new macro

Now inspect the recorded macro and you have the syntax for the various operations.

Bye

 

[Siddharth Sarvoday]

Hi Anthony,
Am looking for changing it to dynamic.. i got stuck at this point of my project.. and am new to this scenario..

 

[Siddharth Sarvoday]

I hope anybody can help me with this...

 

[Anthony47]

Hi Anthony,
Am looking for changing it to dynamic.. i got stuck at this point of my project.. and am new to this scenario..

Would it be ok if the source data link is updated whenever you activate the pivot table sheet? Do you already have a macro that updates the link, even though not dynamically?

Bye

 

[Siddharth Sarvoday]

Hi Anthony,
Lets take example here!!
One Tab(Pivot Name:- PVT) i have pivot which is linked to main Data (Sheet1) but in next month i have to update the new data in (Sheet2) and the same PVT (pivot) needs to be updated breaking the link to Sheet1 Tab

Here the pivot needs to be updated only to Sheet2

I hope you understand above details... thanks-

 

[Siddharth Sarvoday]

Anybody can help me asap please!

 

[Anthony47]

I am sorry I missed the notification for your message.

Record a macro while you, starting from the sheet containing the new source data:
-select the pivot table sheet and object
-modify manually the source data
-reselect the sheet with the data

Then publish the code that was recorded; we will modify it to make it dynamic

Bye