Excel 2010 Dynamic Autofilter on Change

B

bgoree09

New member
Joined
Aug 20, 2013
Messages
179
Reaction score
0
Points
0
Good morning,

I have a workbook with various sheets containing extractions, user inputs, and various formulas. In Sheet2, I have a report that is generated based off of the information within some of these other sheets. The length of this report is currently maintained manually with a filter. The filter field is a helper that uses a formula to decide if the row is valid or not (1 or 0). What I would like, is to have a little bit of background VBA code that will envoke the filter whenever the values in this filter field change. The filter criteria is always 1, the row header is always T12 and the filter range is always T12:T141 (all within Sheet2). It doesn't seem like this would be that hard... but I can't make it work. Thoughts?

Thanks,
 
try:
Code: 
Private Sub Worksheet_Calculate()
AutoFilter.ApplyFilter
End Sub
in sheet2's code-module.
 
Thank you for the suggestion, but this didn't work as needed.
 
bgoree09 said:
Thank you for the suggestion, but this didn't work as needed.
Click to expand...
Well it did here when I tested it.

You're not giving much information when you state 'didn't work'.
The helper column contains a formula? Right?
Calculation is set to Automatic isn't it?
Macros are enabled?
The code is in Sheet2's code module?
Application.EnableEvents hasn't been set to false?

Otherwise we could use a sheetactivate event if users change something on another sheet and only switches to Sheet2 to have a look.
Or a sheetchange event on one or all of the other sheets to execute a Sheets("Sheet2").autofilter.applyfilter line.
 
Hello again,

Sorry for the lack of information. It turns out that I did have the code in the wrong place. Originally, the formula did nothing (since I had it in the wrong location). I have moved it to the Sheet2 Module, but it appears to loop infintely (Run-time error... Method 'ApplyFilter' of object 'AutoFilter' failed). The answer to all of your questions is yes, currently. The most reasonable location for changes to the helper column is contained withing Sheet2. It is not inconceiveable that information in Sheet1 could be changed to effect this column, but I'm much less concerned with it, since it would involve a deviation from standard practice.
 
try:
Code: 
Private Sub Worksheet_Calculate()
On Error GoTo here
Application.EnableEvents = False
AutoFilter.ApplyFilter
here:
Application.EnableEvents = True
End Sub
An autofilter needs to be already on the sheet and filtering something; this code just re-applies the filter (refreshes it, if you will).
 
Last edited:
You must log in or register to reply here.
Back
Top