• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

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

1 Solution
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


Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now