Set Multiple Pivot Tables To 1 Source Range

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.

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: 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

pt.sourcedate = ptmaster.sourcedata

line

This is what i am trying to run:

Sub 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.