Pivot Table filter changes when source data is updated

Hi Giaudi,

Doing a little testing, it looks like xl2007 retains selected filtered items, and doesn't select newly added items if you merely refresh and/or change the data source.

Your macros might be clearing the filter and then reseting it. Can you post the macros you are using to update the data and refresh the pivot tables? There might be a step that is resetting the filters that can simply be removed.

One way. Add another table to the source data, listing just the particular accounts you want to see. Then set up the dataset with SQL like
Then no filters are needed in the pivot table as the data source for the pivot table will contain only the wanted accounts.

Thanks guys!

To import new data, the macro opens the daily file and...

Range("L2").Select
Range(Selection, Selection.End(xlDown)).Copy
Range("M2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("M2:M3000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"N2"), Unique:=True


and to refresh pivot tables..

ThisWorkbook.RefreshAll


Fazza's solution seems a bit out of range (no SQL skills), but I will have a play around and see if I work something out

G

Fazza- Thanks for sharing that approach. I had thought SQL was only used for database application sources like Access; but your comments led me to learn it can be useful for queries of Excel data sources too.

Giaudi- When I use your code with some sample data, my Pivot Table still retains it's filters. I'm assuming that Row 2 is a header row for your Pivot Table data; but it isn't clear to me which Column(s) your Pivot Table is using and which Field is the filter that isn't retaining it's selection. That makes it more difficult to try to replicate the problem you describe.

I'd be glad to look at your workbook if you can post a link, or exchange through a PM.