How to refresh Data for advanced filter and associated problems

**Gooford** · 05-28-2010, 08:49 AM

Please find attached a file I am working on which is initially splitting data up into different tabs according to cost centres.

Once the data has been sorted to a particular tab, that information is then further broken down using advance filter.

I cant find an obvious way of refreshing the filtered data once a new record has been added so I have created a macro to do this.

I want to have the various 'Activity Codes' filtered into groups going down the sheet, however as soon as I press the red button to run the macro I lose all data below the first section.

I can see how to get around this by putting the data across sideways rather than downwards but I would rather not go this way.

Please see attachment

Any help much appreciated

**pb71** · 05-28-2010, 05:31 PM

Your method also relies on you having a worksheet with the Cost Centre name set up before the data is entered. You could run the Advanced Filter from the Master sheet after you have added a new record using your user form (assuming the data is just added to you would not have to clear the contents on the Cost Centre worksheets before running the Advanced Filter).

You could also create worksheets automatically before transferring data if new Cost Centres are added. I have attached a modified workbook as a starting point for you that adopts this idea. It runs off a button on the Master sheet but you could integrate it to run from your user form after you have added data. I have also added a data sort following the advanced filter as this would be the easiest way to group the data by Activity Code.