Retain pivot table filters

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,922
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have a workbook that I paste data into 5 sheets and have another sheet that contains several pivot tables that relate to these 5 sheets.

I'm using the following code (part of the overal procedure) to refresh the pivot tables. This works fine, however, the filters I previously had applied seem to reset themselves:
Code:
With wkbk 'Main workbook used in my procedure
    .RefreshAll
    For Each pPivotTable In .Sheets("Pivots").PivotTables
        With pPivotTable
            .RefreshTable
            .Update
        End With
    Next pPivotTable
    .RefreshAll
End With
I've not been able to find online any code that will refresh the pivot tables but retain the applied filters. Does anyone know how to achieve this?

Thanks in advance,
Jack
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
For me in Excel 2010 filters are retained with:

Code:
    With wkbk 'Main workbook used in my procedure
        For Each pPivotTable In .Sheets("Pivots").PivotTables
            With pPivotTable
                .RefreshTable
            End With
        Next pPivotTable
    End With
 
Upvote 0
That's what I'd read but when I tried it (and again just now to be sure), the filters aren't retaining their values. Nothing else is happening with sheet Pivot.. odd behaviour.

(Forgot to mention, I'm using Excel 2010)
 
Upvote 0
Didn't try it in a new workbook (next step to do) and have no event code assigned anywhere in the workbook. Will update after trying it in new workbook, thanks for suggestion Andrew.
 
Upvote 0
Hi Andrew, got it working; there was issue with the imported data which meant the filters on the Pivot-table were not valid. I'm guessing this is why they appeared to 'reset' themselves?
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top