Pivot table referring to old data source

[sureshmandadpu]

Hi all.

I have certain data linked to 20 pivot tables. When I copy and rename my file, new file pivot tables refer to old file data only. I had to change the data source of 20 pivot tables. Changing data sources each time becomes a tough task. Please help me out.

Regards
Suresh M

 

[sureshmandadpu]

By the way, I am using MS Office 2016.

 

[xlstime]

one suggestion, you can make your pivot table dynamic or take reference from the table.

For more information, please refer below Url to make the dynamic pivot table.

http://excelpivots.com/excel/pivot_table_expanding_data_ranges/

By the way, I am using MS Office 2016.

s

 

[pecoflyer]

Perhaps http://contextures.com/xlPivot04.html

 

[shamal]

I have been using pivots for a long time but today I feel the need to add the source to my pivot table as there are many pivots on my sheet that come from different tabs. I have named my data ranges, however can someone tell me how can i with the help of formulas tell which pivot come from which range or tab? I don't want to go to each pivot and check range and then type it out on my summary sheet.
Thanks.

 

[Monty]

Hello Shamal.

The best way to find data range and tab would be.

Sub Updaterange()

Dim Data_Sheet As Worksheet
Dim Pivot_Sheet As Worksheet
Dim StartPoint As Range
Dim DataRange As Range
Dim PivotName As String
Dim NewRange As String
Dim LastCol As Long
Dim lastRow As Long

'Set Pivot Table & Source Worksheet
Set Data_Sheet = ThisWorkbook.Worksheets("PivotTableData3")
Set Pivot_Sheet = ThisWorkbook.Worksheets("Pivot3")

'Enter in Pivot Table Name
PivotName = "PivotTable2"

'Defining Staring Point & Dynamic Range
Data_Sheet.Activate
Set StartPoint = Data_Sheet.Range("A1")
LastCol = StartPoint.End(xlToRight).Column
DownCell = StartPoint.End(xlDown).Row
Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol))

NewRange = Data_Sheet.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1)

'Change Pivot Table Data Source Range Address
Pivot_Sheet.PivotTables(PivotName). _
ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange)

'Ensure Pivot Table is Refreshed
Pivot_Sheet.PivotTables(PivotName).RefreshTable

'Complete Message
Pivot_Sheet.Activate
MsgBox "Your Pivot Table is now updated."
End Sub