Best way to gather/sort/filter from multiple sheets

Hello all,

I'm trying to come up with the best way to filter and sort data that is on multiple sheets.

The data I have comes from 3 different sources that I copy and paste into one workbook. The columns are always the same but the amount of rows will vary.

I'm looking for the best way to simplify the data for each Organization that I send it to.

I've made a macro for each Org that filters and sorts each set of data for that Org, but as the data changes the macro doesn't and if one Org doesn't have any data in one of the lists for a given month, it gives an error.

I've made a pivot table and had a macro sort the data for each org, but if one Org doesn't have any data in one of the lists for a given month, it gives an error.

To summarize, I'm trying to create a Button that each Org can click and see their data (organized) from the 3 sheets in the workbook.

The problems I've encountered...
1. The data changes in the amount of rows from month to month.
2. If there is no data for one of the Organizations in a given month, the macro throws an error.

I've attached a simple version of my workbook.

I've figured out the dynamic data piece.

I just need help with the VBA portion now. When I run my macro and it filters the data, everything works fine unless there is no data for one of the organizations.

With no data, the organization will not be in the list to pick from.

This is the code i have...

Please Login or Register  to view this content.

In this example, as long as Epcot is in my list, I'm good. However, if there's a month where Epcot didn't have a data point, Epcot won't be in the list. This is causing the macro to error.

I'd like for it to select (blank) if Epcot is not available.

Hi jme1013,

As you have discovered attempting to Assign a 'Filter Name' that DOES NOT EXIST can corrupt the 'Filter Cache'.

I accidentally discovered that the 'PivotFilter Cache' can be repaired if the 'Missing Filter Name' is known.

Please Login or Register  to view this content.

If there is only one Pivot Table on a sheet it can be accessed as:
ActiveSheet.PivotTables(1) instead of ActiveSheet.PivotTables("PivotTable13")

The solution is to do some defensive programming to prevent 'Pivot Cache Corruption' from occurring. See the following code excerpts (non-working - needs additional resources). Working code with the identical code is in the attached file which uses a 'Pivot Table' derived from your sample 'Travel' Worksheet.

Please Login or Register  to view this content.

Lewis