Using VBA Macro two filter 2 tables on the same sheet

[Excel_User_10k]

Hello "Mr Excel",

I come to you again for your wisdom haha. I want two tables - One for Targets, and another for Month To Date Sales. Inline with each other as they contain the same structure but separated by a couple of rows. I currently have the following Macro code in place to filter the Target table by the location entered in C6. This is so it only shows the data relevant to that location and so they cannot see each others. I understood the usual rule of "1 filter per sheet" and when I try to copy/paste to create the 2nd table underneath, it causes an issue with the Macro of course. I have uploaded an image of how I would want it to look. Is there away for both of these tables to be filtered at the same time?

This is the Macro Code I am using:

Private Sub Worksheet_Calculate()

Range(Range("B7"), Range("B7").End(xlDown)).AutoFilter Field:=1, Criteria1:=Range("C6").Value & "*"

End Sub


Hopefully it is an easy solution. Thank You.

 

[Alex Blakenburg]

Your best bet is to convert both your tables to Excel Tables (give them meaningful names) and then you can change your filter line to something like this:

Me.ListObjects("YourTableName").Range.AutoFilter Field:=1, Criteria1:=Range("C6").Value & "*"

Before the ListObjects you need the sheet reference but because you are in a Worksheet Event Module you can just use Me.

 

[Excel_User_10k]

Ah ok, great. Thank you for your help Alex.

So the full code would be as follows?

Private Sub Worksheet_Calculate()

Me.ListObjects("YourTableName").Range.AutoFilter Field:=1, Criteria1:=Range("C6").Value & "*"

End Sub

 

[Alex Blakenburg]

That replaces the line you had in your original post and you would still need to put in the name of the Table you are putting the filter on, where I have YourTableName.

I would not consider it to be the full code.
You should also check if any filters are already applied and if there are any, then most likely do a ShowAllData.

I am also not sure about putting it in a Calculate Event, it depends on when you want the filter to run.
It is more common to use something like a Change Event and test that (in your case) C6 was the cell last changed.

 

[Excel_User_10k]

Hi Alex,

Of course yeah I understand.

You can only have one filter active on a sheet at a time can't you? That is the issue. So the only filter I have had active is the one with that code. This would take over that filter anyway wouldn't it?

Yeah I tried it as an Event at first but it is a Formula that updated in the C6 cell, and unfortunately the Event code doesn't work for that, so I was advised the the only option was to use a Calculate code instead. I understand this can slow things down as it is constantly refreshing but I have to do it that way is C6 is entered based on two codes entered in two other fields. Sort of like a Location number and then a Security number.

 

[Excel_User_10k]

Hi Alex,

Of course yeah I understand.

You can only have one filter active on a sheet at a time can't you? That is the issue. So the only filter I have had active is the one with that code. This would take over that filter anyway wouldn't it?

Yeah I tried it as an Event at first but it is a Formula that updated in the C6 cell, and unfortunately the Event code doesn't work for that, so I was advised the the only option was to use a Calculate code instead. I understand this can slow things down as it is constantly refreshing but I have to do it that way is C6 is entered based on two codes entered in two other fields. Sort of like a Location number and then a Security number.

On a Side note, I don't suppose you would know how to disable the Formula Bar and Headings Bar check boxes in the View tab once the Sheet and Workbook are protected? Thank you.

 

[Alex Blakenburg]

You can only have one filter active on a sheet at a time can't you?

You can only have one sheet filter on a sheet at one time but you can have multiple Excel Tables on the one sheet and each table can have a filter active.

I take it you didn't do this step in my post # 2
"convert both your tables to Excel Tables (give them meaningful names)"

Here are a couple of references:
Excel Tables | Exceljet
How to Create Excel Tables and Fix Excel Table Problems

This would take over that filter anyway wouldn't it?

Filters on different columns are cumulative. If you don't have filters on "any other columns", then the new filter would take over the previous filter.
If you did have filters on other columns those restrictions would remain. ShowAllData will clear all existing filters on all columns.

 

[Excel_User_10k]

Hi Alex,
Yeah sorry, I didn't end up creating two tables because it wouldn't have worked for my next task. I forgot I needed data for 12 mths in this kind of way. Again only showing certain data based on "login" ID's. So I have introduced a drop down box that appears to be able to select the Month, which ties in with a new column I have had to Insert next to the town which I am currently filtering with the VBA code. I now need to change the code to filter by location first and THEN the month that will show in E7 in the same manor. I wish I understood the coding language better because again I am sure it will be something simple but I cannot get it to work. I tried putting 'Then' and copying the Range line but changing the references but it doesn't working. Tried doing it without 'Then' and it doesn't work. I can't figure it out.

So the code is now:

Private Sub Worksheet_Calculate()

Range(Range("C7"), Range("C7").End(xlDown)).AutoFilter Field:=1, Criteria1:=Range("D6").Value & "*"

End Sub

How do I incorporate the 2nd criteria of the Month in the range of "B7" and the Criteria Range ref as "E7"

Thank you very much.

I tell you the amount of roadblocks, speedbumps and hurdles I am coming across for this project. No to mention the headaches this is causing me haha. And I could do with finishing it for October.

 

[Alex Blakenburg]

The image in your post #1 was very small and too blurry to make out when magnified.
Can you provide an XL2BB of a sample of your data ?

You are showing 2 tables. Does your reference to 12 mth means there will be more tables down the page ?
Did you want to apply the same filter to both tables ?
Are the tables after the first one going to keep moving further down the page ?

I hope you have used Centre across selection for C8:D8 and C48:D48 and not merged cells.

 

[Excel_User_10k]

Apologies. I Have attached a better photo that can hopefully help you see what I am trying to explain.

This is the code I am [trying] to use for the Filtering. It was working but now it isn't as the second range is highlighted in Yellow, but I am not sure what is wrong with it now. Please can you explain where I have gone wrong? Also, before I added the 2nd Range, when D6 was blank, it would clear the filter and show everything without me having to unprotect she sheet, unhide the filtering columns, and then clear it manually. Which was useful. Would it be possible to get that back as well?

Private Sub Worksheet_Calculate()

Range(Range("B7"), Range("B7").End(xlDown)).AutoFilter Field:=2, Criteria1:=Range("D6").Value & "*"
Range(Range("B7"), Range("B7").End(xlDown)).AutoFilter Field:=1, Criteria1:=Range("E7").Value & "*"

End Sub

The whole 12mths are on the same sheet, underneath each other. So the filter applies to the whole thing, which I want. D7 remains static and then the rows below are shown for the Team based at the Location and Month selected. And it ALMOST seems to be working the way I want.