Change SourceName on Pivot Table
 

Hi All,

I have a pivot table, where one of the data source column headers
changes. When this happens, and I go to Refresh my pivot table, that
particular column is removed. After doing some research, it sounds
like I need to change the SourceName on that field to match the source
data column header. I tried the following, but got the error 'Unable
to change the Source Name property of the PivotField Class'. Any
ideas?

Sub test()

Set i = Worksheets("Consolidated").Range("M1") 'the data
source header cell that changes

Worksheets("Summary").PivotTables(1) _
.PivotFields("Category").SourceName = i


End Sub