Pitovt Table Datasource selection

Posted on 2008-11-17
Last Modified: 2012-05-05

Right now the pivot tables on tab 2a, is based on tab 2a which sources from Sheet 2. However, if I wanted to change the source to sheet 1, how do I get the pivots tables on tab 2a to update? i have tried the following code, but it reselects the datasource for all pivot tables, making the file too big. Thanks
Sub UpdateAllPivotTableSource()

 Dim WS As Worksheet, PT As PivotTable

 For Each WS In ActiveWorkbook.Sheets

  For Each PT In WS.PivotTables

   PT.SourceData = _


  Next PT

 Next WS

End Sub

Open in new window

Question by:derekaly
    1 Comment
    LVL 9

    Accepted Solution


    Attached is a sheet. I have created a defined name "PivotSource" and the Pivot Table Source has been changed to "PivotSource" instead of Sheet1!A:E. The above range has been defined in such a way that the range is dependent upon the "SHEET NAME" selected from Cell F1 in Sheet 2a. Select the name from list , right click anywhere IN the table and then click "Refresh". The Pivot will update to reflect summary from new Data Source. You can check the defined name from Insert---------->Names----------->Define------------>Select "Pivot Source"

    To avoid clicking refresh every time you can choose
    Private Sub Worksheet_Change(ByVal Target As Range)
    event in Sheet 2a

    CA Kanwaljit Singh Dhunna


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now