How can I duplicate a sheet with a pivot table and use the data source from that new sheet?

Question

I have a pivot table within the same sheet where the data source is located, lets call it sheet Week (3). the data source range is 'Week (3)'!$G:$P. If I duplicate the sheet it create a new sheet called Week (4), but the range of the pivot stays as 'Week (3)'!$G:$P, how can I set the data range to match up the name of the new sheet? In that case it should be 'Week (4)'!$G:$P. Is there a formula or way to make it change automatically to the current sheet?

Answer 1

You may need to change data source manually.

Select the new povit table in new sheet> Povittable analyze> Change data source>Select the Week (4)'!$G:$P.

There is no formula can change data source of a povit table.

If you want an automatically way, macro can check if it is using same sheet data source. If not, change data source to current sheet.

Answer 2

Hi, thanks for your response, I know I can change it manually. I know how to do it, I was trying to see if there was an automatic way to do it.

Answer 3

You may try this macro, it will set all the povittable data source to current sheet column G to Column P

=====================

Sub CheckPivotTables()

Dim ws As Worksheet 

Dim pt As PivotTable 

Dim ptCache As PivotCache 

For Each ws In ActiveWorkbook.Worksheets 

    For Each pt In ws.PivotTables 

            pt.SourceData = ws.Name & "!G:P" 

    Next pt 

Next ws 

End Sub

=====================