Setting Filters in different rows automatically

Dear All,

I am looking for Code which sets a filter depending on the value of cell D2 in different columns.

If D2 equals All no filter is set or a set filter is cleared.
IF D2 equals A the filter in column D is set to 1.
IF D2 equals B the filter in column E is set to 1.
...
IF D2 equals G the filter in column I is set to 1. If D2 contained a letter beforehand the filter of the respective column has to be cleared. Only the filter in column I can be set.

I have an idea how to set a filter in one column. But in this case I need the code to change the filtered column depending on the value. Also I have the problem that the code below is fixed to Table1. Since the worksheet will be copied I have the problem that the reference to the new table needs to modified automatically. Is it possible to automate it?
Another problem is that the code only accepts numbers for the different cases and no letters.
Finally the code produces errors whenever I add a new row or change the content of a cell. Why is this happening?

Please Login or Register  to view this content.

Please find the example attached.

Many thanks.

Best regards,
fxmu

Hi Bernie,

thanks very much. Unfortunately the code turns off the autofilter only. But the autofilter needs to work with the other columns.

Best
fxmu

That is a very smart solution. But my example was again misleading. A, B, C etc. were only examples. For the actual sheet I have to use other headings (such as property or bank) and another drop down accordingly. Unfortunately the logic with the alphabet isn't working then, as I have learned (:. Is there another solution for it and is it possible to only clear the filter when "all" is selected and not delete the filter functionality?

Book1 (4) v2.xlsm

Just because I'd like to understand it - why does column A receive the number -64?

Best
fxmu

This works perfectly fine. Awesome!