Hello -
Hoping someone can help me with my question regarding source data and pivot tables. One a tab I have columns of data which is the source data for a pivot table I have just below. For the sake of this question, lets say all of this is on "Sheet1", the data is in cells A2:F25, and the pivot table is in cell G27.
The problem comes when I make a copy of the tab (keeping the copied tab in the same workbook), change the data in this new tab (called Sheet2), the pivot table still uses the data from Sheet1 (it seems to automatically anchor the data source to that original tab). Is there a way that I can run a macro so that it will change the data source for the pivot table in Sheet2 to run off of the data in Sheet2.
The reason I don't want to manually adjust the source data is that I will have many tabs and hoping there was a macro that I could run on each tab to change the data source. Hopefully this question is clear enough that someone can help.
I don't know if it makes a difference, but I am in Excel 2010
Thanks!
Hoping someone can help me with my question regarding source data and pivot tables. One a tab I have columns of data which is the source data for a pivot table I have just below. For the sake of this question, lets say all of this is on "Sheet1", the data is in cells A2:F25, and the pivot table is in cell G27.
The problem comes when I make a copy of the tab (keeping the copied tab in the same workbook), change the data in this new tab (called Sheet2), the pivot table still uses the data from Sheet1 (it seems to automatically anchor the data source to that original tab). Is there a way that I can run a macro so that it will change the data source for the pivot table in Sheet2 to run off of the data in Sheet2.
The reason I don't want to manually adjust the source data is that I will have many tabs and hoping there was a macro that I could run on each tab to change the data source. Hopefully this question is clear enough that someone can help.
I don't know if it makes a difference, but I am in Excel 2010
Thanks!