I have Pivot Table that get its data source from another sheet, same workbook. I want the sort order of data is same as the data source order, I choose "Sort in data source order" in Pivot Table option.

The problem is, when I change the data order on data source worksheet, then I refresh the Pivot Table, the sort order didn't change.

I googled that the Pivot Table should be unlink first then re-link again in order to work properly, so I tried the following:

The original data source has named range: origdata. The fake data source has names range: dummydata

I changed manually data source to dummydata then changed back to origdata. The sort order did change as expected. Now I want to make the operation automated, so I'm using this code in Worksheet.activate event. Note that, PT is PivotTable instance.

...
PT.SourceData = "dummydata"
PT.RefreshTable
PT.SourceData = "origdata"
PT.RefreshTable
...

Change data source from VBA didn't change the sort order just like I did with manual method. Why is that? Am I missing something? Maybe there are some routine called when I changed the data source manually via toolbar button? Thanks in advance for your help.