Automatic update/repeat of advanced filter?

Hi all,

I'm using the advanced filter to copy certain rows of data containing a "Y" in one column into a second sheet - this is for use on a dashboard and those "Y"s appear against different records according to which week/teacher/year group etc the user selects on the main page. Of course the one (fairly major) problem that I'd missed when I started making it is that the advanced filter doesn't automatically update!

So, I was wondering whether there might be a clever way that VBA could be used to update/re-run an advanced filter whenever a change is made to other worksheets? I've searched for quite a while but only managed to come up with one that works on the standard filter so far, sadly my knowledge of the coding side of Excel is a bit limited

Thanks for reading

Adam

Hi Adam
If there is nothing sensitive in the workbook can you post a copy and I will take a look for you.
Tony

Hi Tony,

Thanks for such a quick reply I'm afraid it's school data though (student details etc) so all rather sensitive and there's too much to be adding dummy data to it...

If it helps to picture what I'm doing at all, this is a very quick sketch of what the dashboard looks like - there will be more tables than that in reality, but if I can get one working the rest shouldn't be an issue

DASHBOARD.jpg

So, 3 pivot table slicers filter the attendance data, controlling what is displayed in the totals and pie charts at the top of the page (allowing it to be broken down by year group, week and mentor group) - this bit is already done. However, what they also want on the same page is lists of student records (behaviour logs, achievements etc) following the same filters - there will be 5 or 6 of these so in order to be user-friendly they will also need to be filtered by the slicers rather than the user and the additional data is not in a suitable form to add to the pivot tables. That's where my "Y" column comes in, it identifies whether each individual student record matches the way the pivot table is filtered.

For example, the student behaviour records output from the school MIS would look something like the one shown below (far more columns&rows of course, but just to give an idea!), with the "Y" column then added in by formulas and only becoming marked when the data matches all the buttons selected on the slicers. So before applying an advanced filter I have something that looks vaguely like this;

BEFORE.jpg

And afterwards I'm wanting to have it looking like this;

AFTER.jpg

So now, I only have the records appropriate to the current filters, with no blank rows between them, and at the same time no data has actually been deleted either - it's all still on the original sheet. Those conditions are important for me to get the scrolling boxes working and at present an advanced filter is the only way I see to can get to that result with my level of Excel understanding (that is, reasonably good other than the macros/vba bit ). It does work, the only issue being that I don't want the user to have to go off the main page and refilter several sets of data every time they want to look at something new - half of them would probably need training to even attempt that anyway An automatically updating filter seems like the ideal solution if it's possible. I would just use the code I found that works on a standard filter, but that still leaves me with the unwanted rows in between

Hi Adam
OK, what you can do is create code for a Worksheet.Change event. SO that whenever something changes in a given cell or range of cells this macro will run. So if you record your macro to run the filter that you then paste that code where indicated in the sample code below: Also where it has "A1" on the second line change that to the range that you wish to use for this macro to activate when something changes in that Range, for a column it could be C:C or for a cell B2. Once you have pasted your code where indicated copy the code and right click on the sheet tab that the filter is created on and click on "View Code". Then paste this code there and save the workbook and close that view down. Now whenever something changes in the range you specify on the second line this macro will automatically run the filter again and refresh it.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
'Paste your filter code below this line

Application.EnableEvents = True
End Sub

Thanks for the reply, I've added the code into yours but it doesn't trigger the filter, does this look right to you? The sheet to be filtered is called DForm and the "Y" is displayed in column U (checked and that column is definitely recalculating properly when the main page is filtered, it also doesn't make any difference if I type directly into the column), it copies the filtered data to the same range in the DFormOUT sheet.

Please Login or Register  to view this content.

I wondered whether it might be that annoying "feature" where Excel makes you run an advanced search from the destination sheet, but I put the code I got from the macro into a module and can run it from any sheet without difficulty. Perhaps an alternative if this can't be made to work automatically (although I may well have made a mistake with the above ) would be for me to just link it (plus the other 5 that I'll need) up to a "refresh" button on the main page - not perfect perhaps but it's a definite improvement

Hi Adam
Did you put the code I gave you in a Module? If so it needs to go behind the Worksheet (right-click on the sheet tab and click on View Code, then paste the code there).
Let me know.
Tony

Hi Tony,

No, it's definitely in the worksheet, the module idea was just to test whether the code I'd got out would actually work from other sheets.

I've quickly put together a version using that update button idea (thanks for the inspiration ), not quite finished but I'm happy enough with that to be honest - it still works well and looks ok to me. I don't want to waste your time if it's going to be a difficult one!

DASHBOARD.jpg