Im using MS Excel 2003, have multiple pivot tables of which the data source has changed. I have connected the pivot tables to an external database and it seems to be trying to refresh the same data for multiple pivots although it is the same data source. I know you are able to look at the same pivot table within the workbook by using the wizard and going back to select 'another pivottable report or pivot chart report' by looking at the first pivot table refreshed, however there are over 80 pivot tables and i was wondering if there was a quicker way this could be done through using VBA or even through the excel interface.
Set Multiple Pivot Tables To 1 Source Range
-
-
-
Re: Multiple Pivot Tables One Data Source
While this is a very nice statement of fact, I fail to see a question to answer.
Please clarify.
Regards,
-
Re: Multiple Pivot Tables One Data Source
apologies, for some reason it didnt save the question i posted but ive edited and saved it now and it appears to be there, just in case it isnt:
Im using MS Excel 2003, have multiple pivot tables of which the data source has changed. I have connected the pivot tables to an external database and it seems to be trying to refresh the same data for multiple pivots although it is the same data source. I know you are able to look at the same pivot table within the workbook by using the wizard and going back to select 'another pivottable report or pivot chart report' by looking at the first pivot table refreshed, however there are over 80 pivot tables and i was wondering if there was a quicker way this could be done through using VBA or even through the excel interface.
thank you for your time.
-
Re: Multiple Pivot Tables One Data Source
Try;
Code
Display MoreSub SetAllPtTo1Source() Dim ws As Worksheet Dim pt As PivotTable, ptMaster As PivotTable Set ptMaster = Sheet4.PivotTables("PivotTable1") For Each ws In Worksheets For Each pt In ws.PivotTables If pt.Name <> "PivotTable1" Then pt.SourceData = ptMaster.SourceData End If Next pt Next ws End Sub
-
Re: Set Multiple Pivot Tables To 1 Source Range
Hi Dave,
Thank you for the code, however i seem to be getting a object defined error at the
line
This is what i am trying to run:
Code
Display MoreSub SetAllPtTo1Source() Dim ws As Worksheet Dim pt As PivotTable Dim ptMaster As PivotTable Set ptMaster = Sheets("Total Pivots").PivotTables("Stats main") For Each ws In Worksheets For Each pt In ws.PivotTables If pt.Name <> "Stats main" Then pt.SourceData = ptMaster.SourceData End If Next pt Next ws End Sub
[/Code]For Each pt In ws.PivotTables[/Code], does this line of code go through the worksheets regardless of the name of the worksheet and select those pivot tables? Thank you for your time.
-
Re: Set Multiple Pivot Tables To 1 Source Range
Is the line that goes through each worksheet in the Worksheets Collection, regardless of name.
takes each pivot table in the PivotTable Collection in the currently active worksheet.
Regards,
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!