Retain pivot table filters

[JackDanIce]

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:

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

 

[Andrew Poulsom]

For me in Excel 2010 filters are retained with:

    With wkbk 'Main workbook used in my procedure
        For Each pPivotTable In .Sheets("Pivots").PivotTables
            With pPivotTable
                .RefreshTable
            End With
        Next pPivotTable
    End With

 

[JackDanIce]

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)

 

[Andrew Poulsom]

Did you try it on a new workbook? Do you have any event code assigned to the pivot tables?

 

[JackDanIce]

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.

 

[JackDanIce]

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?