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]

I don't understand, at no point did I mention changing something to Select.
The only change you should have made in both sheets was to replace If statement line below this comment line
' If Store criteria no Invalid ID apply Store filter

with this line
[B] If Range("D6") <> "" Then[/B]

ie MTD

        ' If Store criteria no Invalid ID apply Store filter
        If Range("D6") <> "" And Range("D6") <> "Invalid ID" Then
        If Range("D6") <> "" Then

Targets

        ' If Store criteria no Invalid ID apply Store filter
        If Range("D6") <> "Invalid ID" Then
        If Range("D6") <> "" Then

Oh sorry, I misread that. I have changed it now. Thank you.

So that fixes the default viewing. But I can still choose a month and it shows it all when G3 and G4 are blank.

It should go Store code/Secure ID > Month selection > Reveal relevant rows. And if the Month is on Select, it will show all the rows (the full year) for that Store.

At the moment, if the secure id is deleted or changed (which means D6 has now changed back to "Invalid ID") then it will STILL shows the rows. If the month is selected but no value is entered in G3 or G4 at all (i.e. and so again D6 still says "Invalid ID") it still filters to show the rows but for ALL stores in that month.

Basically as long as D6 says "Invalid ID", no rows should show at all. Whether the month is selected or not.

Because unfortunately anything else completely undermines the whole point of setting it to filter based on entering the two codes.

 

[Alex Blakenburg]

I'm afraid that still means you have changed something and not put it back the way it was.
I am login off for the night below is the code I am using and it will not show any lines if D6 says invalid regardless of what you have in the month box.
What it essentially does is try to filter on INVALID ID which doesn't exist so it doesn't show anything.

Please don't try to fix what you have and just replace it with this.

Targets

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngToFltr As Range
    
    Application.ScreenUpdating = False

    If Not Intersect(Target, Range("J4")) Is Nothing Or _
       Not Intersect(Target, Range("G3")) Is Nothing Or _
       Not Intersect(Target, Range("G4")) Is Nothing Then

        With Me
            Set rngToFltr = .Range("B7:C" & .Cells(Rows.Count, "B").End(xlUp).Row)
        End With
    
        If Me.FilterMode Then Me.ShowAllData
    
        ' If month selected apply month filter
        If Range("E7") <> "" Then
            With rngToFltr
                .AutoFilter Field:=1, Criteria1:=Me.Range("E7")
            End With
        End If
        
        ' If Store criteria no Invalid ID apply Store filter
        If Range("D6") <> "" Then
            With rngToFltr
                .AutoFilter Field:=2, Criteria1:=Me.Range("D6")
            End With
        End If

    End If
    
    Application.ScreenUpdating = True

End Sub

MTD

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngToFltr As Range
    
    Application.ScreenUpdating = False

    If Not Intersect(Target, Range("L3")) Is Nothing Or _
           Not Intersect(Target, Range("G3")) Is Nothing Then

        With Me
            Set rngToFltr = .Range("B7:C" & .Cells(Rows.Count, "B").End(xlUp).Row)
        End With
    
        If Me.FilterMode Then Me.ShowAllData

        ' If month selected apply month filter
        If Range("E7") <> "" And Range("E7") <> "Select…" Then
            With rngToFltr
                .AutoFilter Field:=1, Criteria1:=Me.Range("E7")
            End With
        End If
    
        ' If Store criteria no Invalid ID apply Store filter
        If Range("D6") <> "" Then
            With rngToFltr
                .AutoFilter Field:=2, Criteria1:=Me.Range("D6")
            End With
        End If
        
    End If
    
    Application.ScreenUpdating = True

End Sub

 

[Excel_User_10k]

All I did was copy and paste haha.

Ok, opened and closed it a few times to test it. Half the time it works fine, the other half I get the debug message again - and that goes for both MTD and Targets Sheets. Whether I open on that Sheet or a different one and change over to it. Driving me insane now! I feel like it is just being awkward on purpose! Clocking off now. Will have a look again tomorrow.

 

[Excel_User_10k]

I tried opening it on a different computer and at first there was no response at all. Then closed and opened it again and getting that same End/Debug error message ><

 

[Alex Blakenburg]

Did you run the sheet protection macro first ?

 

[Excel_User_10k]

Did you run the sheet protection macro first ?

Yes. Does it matter if some of the Sheets were already protected or anything (still with the same password as in the Macro though)?

 

[Alex Blakenburg]

No. It doesn't seem to matter.

 

[Excel_User_10k]

No. It doesn't seem to matter.

I didn't think it would. So then how is it still not working?? Haha

 

[Excel_User_10k]

You said Calculate was a poor choice for coding. What if, rather than basing the filter on the Store name itself, it is based on the actual values entered in the Store ID and Secure ID? As these are entered manually, it isn't dynamic and the result of a formula and therefore doesn't need to be constantly checking it. This would make it much more stable, correct? If it could be based on those two paired codes and then the month without 'Calculate', would that be better? Could that be why it is failing to be consistent, nevermind actually work?

 

[Alex Blakenburg]

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