I created a spreadsheet to track sales data which contains multiple pivot tables. I intend to save multiple copies of the sheet for individuals to use, then pull all the data into a master workbook.
Here's the problem:
Whenever I erase or replace data the filters reset. For instance, I have the data filtered by month. When I erase the data, those filters go away and it no longer filters by month (instead showing all the data).
Any advice on how to retain the filters would be greatly appreciated.
Why do you erase data in the first place? Part of the power of the pivot table and its filters is that you can keep adding more months of data, even into the next year(s). Just add "YEAR" as another level of filter. Doing this would enable you and your users to compare results from month to month, or same months but different years....and so forth.
You would need to "Refresh Data" whenever the table is extended, but things like filters wouldn't be disappearing.