Trouble Changing Pivot Table Source

[hawkinsr86]

Hi guys,

I have about 8 worksheets in a workbook, all of which have pivot tables that link to the same data set. I want a piece of code that changes the data source of each pivot. Here is the code i am using

Sub RefreshPivotTables()


Dim ws As Worksheet
Dim PT As PivotTable
Dim SourceName As String


SourceName = shtData.Range("N2") ' this value is Data!$B$3:$W$3591


For Each ws In ActiveWorkbook.Worksheets
For Each PT In ws.PivotTables
PT.SourceData = SourceName
Next PT
Next ws


ActiveWorkbook.RefreshAll
End Sub

Thanks in adavnce

 

[hawkinsr86]

forgot to say i keep getting runtime error 1004 - 'the pivottable field name is not valid.....'