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: Multiple Pivot Tables One Data Source


    Try;

  • 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


    Code
    pt.sourcedate = ptmaster.sourcedata

    line


    This is what i am trying to run:


    [/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


    Code
    For Each ws In Worksheets


    Is the line that goes through each worksheet in the Worksheets Collection, regardless of name.


    Code
    For Each pt In ws.PivotTables


    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!