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.

 

[Excel_User_10k]

You are going around in circles, we have addressed all of that. The only issue is how you are applying the protection.
So just add this line after the If Not Intersect line in both procedures/sheets

Me.Protect Password:="myPassword", AllowFiltering:=True, UserInterfaceOnly:=True

Ok.

Right, I have done how you said (and hopefully correctly, as shown in screenshot - and yes I have changed the "myPassword" part, before you ask). Great news is that it seems to be stable and working, no errors! Targets Sheet is doing exactly what I want! Amazing :D!

Unfortunately, the only thing now is that MTD is showing all rows again as default when G3 (Store No) is blank. We have already solved this problem so not sure how it has gone back to doing that?

I take it that this way of doing it was the Plan B that you mentioned earlier, so I don't need the 'Protect All Sheets' Macro now?

 

[Alex Blakenburg]

MTD - Check that when G3 is blank that your formula is still returning "Invalid ID" in D6. If not fix your formula.

Protect All Sheets macro - Correct you no longer need it for the purpose of these 2 macros. (MTD and Targets)

 

[Excel_User_10k]

MTD - Check that when G3 is blank that your formula is still returning "Invalid ID" in D6. If not fix your formula.

Protect All Sheets macro - Correct you no longer need it for the purpose of these 2 macros. (MTD and Targets)

Yeah it does. The formulas all still work as they should. And it still says "Invalid ID" in D6 when G3 is either blank or an incorrect 'ID' is entered. But regardless of either it still Shows All Rows. And the code is still as pictured in #71.

Ok no worries.

 

[Alex Blakenburg]

This line should look like the below in both sheets. It is correct in Targets but not correct in MTD.
If Range("D6") <> "" Then

 

[Excel_User_10k]

This line should look like the below in both sheets. It is correct in Targets but not correct in MTD.
If Range("D6") <> "" Then

Do you mean in the area highlighted? Because it does say that.

 

[Alex Blakenburg]

Yes but replace the line with what I sent you.
ie delete the And Range("D6") <> "Invalid ID"

        If Range("D6") <> "" And Range("D6") <> "Invalid ID" Then

 

[Excel_User_10k]

Yes but replace the line with what I sent you.
ie delete the And Range("D6") <> "Invalid ID"

        If Range("D6") <> "" And Range("D6") <> "Invalid ID" Then

View attachment 75296

Ahhh I see. It wasn't clear what you were getting at there. Thought you were saying that is what it is supposed to say.

Brilliant! Looks like it is working, after about 2 weeks haha. Thanks so much for your continued help and patience.

 

[Excel_User_10k]

Ok, not sure whether to ask this here or start a new thread, but as you are very familiar with this workbook by this point and it is just a simple question about fractions. In the area highlighted in the Screenshot, there are two grey Cells. The top one if a fraction that I want to show how many Greens there are out of all Greens, Ambers, Reds and Blues added together. So if there were 2 Greens, 1 Yellow, 1 Red, 1 Blue, it would say "2/5". I have the formula showing in the other Screenshot, highlighting the part concerning to the month that is selected (as the rest of the formula is only relevant when any of the other months is selected in the drop down box). It basically says Divide [Green] by how many there are when adding [Green], [Yellow], [Red], and [Blue]. However sometimes, especially if there are 0 Greens, it does not seem to understand. Whereas I would expect it to say "0/5" for instance, it will say something random like "0/3" even though there are still a total of 5. I suspect this is because it would be dividing the total by 0, which obviously makes no sense. However, I do not know how else to write the formula to tell it this. Any ideas?

 

[Alex Blakenburg]

I'm afraid you will need to start a new thread for that.

 

[Excel_User_10k]

I'm afraid you will need to start a new thread for that.

Ok no problem. Will do. Thanks again!