jonathanjka
New Member
- Joined
- Mar 16, 2017
- Messages
- 1
Hello all,
I'm pretty green to the VBA pastures, but I've been doing a half-day of research and still hasn't found a perfect solution yet to this. So I'm hoping some geniuses here can help me out
Short version: In forums and Excel tutorials I have found VBA codes that change sources for either all pivot tables, or only one. I'm looking for a solution to changing sources for some, but not all.
I receive data in a worksheet that comes in the same format on a monthly basis. What I do is copy some tabs with pivot tables that reference some tabs within the previous month's worksheet into the new month. I then have to change the source of each pivot table and refresh each of them individually - because although the name of the tab is the same, the copied pivot tables still refer to the previous month's worksheet.
I've researched macros to help me refresh automatically. However, most of the macros I find out there either refresh all pivot tables within a worksheet or only one. The problem is within the same tab, I have some pivot tables that have different sources.
My question is, does anyone have any idea how to change the sources and then refresh some pivot tables at once, but not all within the worksheet? I am thinking to aggregate by data source. So for example if PivotTable 2,3, and 8 connect to source "DATA1" then I would like to write a VBA code that changes the source for only PivotTable 2,3, and 8.
The two codes I have looked at are:
-For changing sources for all pivot tables:
http://www.contextures.com/excelpivottabledatasource.html
-For changing source for only one pivot table:
How To Automatically Update a Pivot Table Range [3 Different Methods]
If anybody can help provide insights on how to modify the codes or to go about this, will be much appreciated!
I'm pretty green to the VBA pastures, but I've been doing a half-day of research and still hasn't found a perfect solution yet to this. So I'm hoping some geniuses here can help me out
Short version: In forums and Excel tutorials I have found VBA codes that change sources for either all pivot tables, or only one. I'm looking for a solution to changing sources for some, but not all.
I receive data in a worksheet that comes in the same format on a monthly basis. What I do is copy some tabs with pivot tables that reference some tabs within the previous month's worksheet into the new month. I then have to change the source of each pivot table and refresh each of them individually - because although the name of the tab is the same, the copied pivot tables still refer to the previous month's worksheet.
I've researched macros to help me refresh automatically. However, most of the macros I find out there either refresh all pivot tables within a worksheet or only one. The problem is within the same tab, I have some pivot tables that have different sources.
My question is, does anyone have any idea how to change the sources and then refresh some pivot tables at once, but not all within the worksheet? I am thinking to aggregate by data source. So for example if PivotTable 2,3, and 8 connect to source "DATA1" then I would like to write a VBA code that changes the source for only PivotTable 2,3, and 8.
The two codes I have looked at are:
-For changing sources for all pivot tables:
http://www.contextures.com/excelpivottabledatasource.html
-For changing source for only one pivot table:
How To Automatically Update a Pivot Table Range [3 Different Methods]
If anybody can help provide insights on how to modify the codes or to go about this, will be much appreciated!
