Pivot Table Source Data changing when sheet copied vba

[cSciFiChick]

So I have a report where the sheet is copied and it has a pivot table. But when I copy the tab the pivot table source refers back to the previous tab. It would be fine if I was working with this report because I know how to change the data source but this will be used by people that are not that familiar with Excel so I want it to be automatic. Below is the macro I have been trying to use but it does not work. The range of the data will always be on the same sheet as the pivot table C5:D15.


Sub Pivot()
Dim WS As Worksheet
Dim PT As PivotTable
For Each WS In ActiveWorkbook.Worksheets
For Each PT In WS.PivotTables
PT.PivotTableWizard SourceType:=xlDatabase, SourceData:="'" & WS.Name & "'" & "!" & WS.Range("C5:D15")
PT.RefreshTable
Next
Next
End Sub

 

[ExcelPowerN]

This should work

Sub Pivot()
Dim WS As Worksheet
Dim PT As PivotTable
For Each WS In ThisWorkbook.Worksheets
For Each PT In WS.PivotTables
PT.ChangePivotCache ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="'" & WS.Name & "'" & "!C15:D15")
PT.RefreshTable
Next
Next
End Sub