Pitovt Table Datasource selection


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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kanwaljit DhunnaCACommented:

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.