Pivottable in another sheet is affected when the activesheet is refreshed

[dandelion]

Hi,

I have 02 worksheets and in each worksheets I have 02 pivottable that are linked to the same source (datatable). In each worksheet, I have a code to have different format in terms of rows, columns... However, when I run the macro in worksheet 1 , the format of worksheet 1 is as expected then I run the macro in worksheet 2, the format of worksheet 2 is as expected however when I come back worksheet 1, the format is totally back to "unformated". How can I stop this?

I tried to use this:
in WS1: ActiveSheet.PivotTables("Main").RefreshTable
in WS2: ActiveSheet.PivotTables("Sub").RefreshTable
I named 02 pivots with the name "Main" and "Sub", respectively

to differeniate 02 pivots and only refresh in that Sheet only; however, it does not work.
Thanks for support!

 

[Alex Blakenburg]

You might have 2 separate issues.
Firstly when you Refresh a Table you refresh the Pivot Cache and every Table using that Pivot Cache gets refreshed.

Assuming the tables are using the same cache, try going into one of the Pivot tables changing the Data Source so it doesn't inlude all the rows or column, refresh it and then change it back to the include all the rows and columns.
This doesn't always work but start with that.
If you know how to use the Immediate window in VBA (ctrl+G if it is not visible), you could put the below in that window and see if the number increases by one after you make the change. (include the ? at the start of the line)

? ActiveWorkbook.PivotCaches.Count