Pivot table not refreshing sort order
- by William Anthony
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.