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]

Code for MTD and Targets Sheets below.

1) To allow filtering to work, when you protect the sheet tick the Allow "Use Autofilter"
2) MTD - will now update if either the Store or Month change
Targets - will now update if either the Store or Secure ID or Month change
3) MTD if Store =1 or Month set to Select... or "" then filter will be off
Targets if Store parameter in D6 = Invalid ID or Month set to Select... or "" then filter will be off
You can play with that to get the desired configuration

MTD Sheet Code

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
  
        ' Conditions under which to leave filter off
        If Range("G3") <> 1 And _
            (Range("L3") <> "" And Range("L3") <> "Select...") Then
          
            With rngToFltr
                .AutoFilter Field:=2, Criteria1:=Me.Range("D6")
                .AutoFilter Field:=1, Criteria1:=Me.Range("E7")
            End With
        End If

    End If
  
    Application.ScreenUpdating = True

End Sub

Targets Sheet Code

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

    If Not Intersect(Target, Range("I3")) Is Nothing Or _
       Not Intersect(Target, Range("E2")) Is Nothing Or _
       Not Intersect(Target, Range("E3")) 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
  
        ' Conditions under which to leave filter off
        If Range("D6") <> "Invalid ID" And _
            (Range("I3") <> "" And Range("I3") <> "Select...") Then
          
            With rngToFltr
                .AutoFilter Field:=2, Criteria1:=Me.Range("D6")
                .AutoFilter Field:=1, Criteria1:=Me.Range("E7")
            End With
        End If

    End If
  
    Application.ScreenUpdating = True

End Sub

Hi Alex, thanks very much for the effort.

Unfortunately it still isn't resolved. Took a few attempts for it to work when unprotected. But it still errors when trying to do it Protected, when when Autofilter is enabled.

They both come up with an error and highlights a part of the code: "Me.ShowAllData".

I thought I had already enabled Autofilter when protecting the sheet so that is a bit embarrassing but I have made sure it is enabled for both now haha. Sorry about that.

It is just refusing to work. VBA is such an awkward pain in the butt!

 

[Excel_User_10k]

I thought I would try the old code again in #36 now that I have enabled autofilter. Still getting that same error though. Why does it not like "me.showalldata"??

I also don't get how a screen shot of the same screen can be "too large" sometimes but fine at other times. I wish things would just work haha ><

 

[Alex Blakenburg]

Can you try setting your protection with the UserInterface Parameter eg

ActiveSheet.Protect "", UserInterfaceOnly:=True

 

[Excel_User_10k]

Can you try setting your protection with the UserInterface Parameter eg

ActiveSheet.Protect "", UserInterfaceOnly:=True

Ok, so I am not sure if I have incorporated this in correctly but it seems to have fixed something.

So I now have...

For Targets:

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.Protect "password", UserInterfaceOnly:=True

    Dim rngToFltr As Range

    If Not Intersect(Target, Range("I3")) 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

    With rngToFltr
        .AutoFilter Field:=2, Criteria1:=Me.Range("D6")
        .AutoFilter Field:=1, Criteria1:=Me.Range("E7")
    End With

    End If

End Sub

And for MTD:

Private Sub Worksheet_Change(ByVal Target As Range)
   
    ActiveSheet.Protect "password", UserInterfaceOnly:=True
   
    Dim rngToFltr As Range

    If Not Intersect(Target, Range("L3")) 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

    With rngToFltr
        .AutoFilter Field:=2, Criteria1:=Me.Range("D6")
        .AutoFilter Field:=1, Criteria1:=Me.Range("E7")
    End With

    End If

End Sub

Ok so it does filter whilst Protected, on both Sheets. Doesn't unprotect the Sheet, doesn't flash up with any rows just by entering the Store code. And hopefully it is stable enough to continue to work when it is passed about and used regularly (I say this because of the amount of times I think something is fixed when using VBA and then after a few uses, it stops or comes up with an error. But anyway so far so good haha)

The only two things now that aren't doing as we planned, and would make it perfect, are
1. I am back to having to reselect the Month before it updates after changing store, and
2. When both reference Cells are clear, it is still locking down the filter instead of clearing/showing all.

I feel like we are on the verge of a eureka moment haha!

Just as an additional observation: any idea why it is doing this when switching Month?

Dropbox - File Deleted - Simplify your life

www.dropbox.com

The staff names change perfectly, but the KPI's kind of jump down and then back up. I prefer it to do it like the Staff Names where it just appears straight away instead of doing whatever it is doing there. It only does this on the Targets Sheet though. MTD works nice and smooth.

 

[Excel_User_10k]

Ok, update: they aren't filtering again...

I am not getting any error messages but nothing at all is happening either. Whether Protected or Unprotected.

I really don't get it. This is exactly what I meant about VBA where it can work for a while then suddenly stop. It is so stupid. And out of all of the things you can do with this coding, this has to be one of the simplest -_-

 

[Alex Blakenburg]

Posting the actual code you are using was really helpful as was the video showing the issue you are having with the screen refresh.

It does beg the question though, why are you not using the code I provided ?
The screen flicker is solved by the turning ScreenUpating off (then back on)

Replace your whole module in the targets sheet with this and then lets see where you land.
The Protect sheet code should not really go in this module but you must have some code somewhere to enable & disable it.
Wherever you are enabling it you need to add the UserInterface parameter.

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

    If Not Intersect(Target, Range("I3")) Is Nothing Or _
       Not Intersect(Target, Range("E2")) Is Nothing Or _
       Not Intersect(Target, Range("E3")) 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("I3") <> "" And Left(Range("I3"), 6) <> "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") <> "Invalid ID" Then
            With rngToFltr
                .AutoFilter Field:=2, Criteria1:=Me.Range("D6")
            End With
        End If

    End If
    
    Application.ScreenUpdating = True

End Sub

 

[Excel_User_10k]

Posting the actual code you are using was really helpful as was the video showing the issue you are having with the screen refresh.

It does beg the question though, why are you not using the code I provided ?
The screen flicker is solved by the turning ScreenUpating off (then back on)

Replace your whole module in the targets sheet with this and then lets see where you land.
The Protect sheet code should not really go in this module but you must have some code somewhere to enable & disable it.
Wherever you are enabling it you need to add the UserInterface parameter.

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

    If Not Intersect(Target, Range("I3")) Is Nothing Or _
       Not Intersect(Target, Range("E2")) Is Nothing Or _
       Not Intersect(Target, Range("E3")) 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("I3") <> "" And Left(Range("I3"), 6) <> "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") <> "Invalid ID" Then
            With rngToFltr
                .AutoFilter Field:=2, Criteria1:=Me.Range("D6")
            End With
        End If

    End If
   
    Application.ScreenUpdating = True

End Sub

Good, I am glad they helped haha.

Ok well that has really confused me. I AM using your code?? But then you mentioned that code in #43 but wasn't sure what you meant or what you wanted me to do with it so I added it in to the code as I thought you intended and then I asked you if that was right! That is the only thing I changed, and that was as per your direction....or so I thought (although I can see you have included it in the code as I thought. I just incorrectly put it at the beginning of the code instead of the end). However, I have since moved the Month drop down box so I updated the code from I3 to J3 and the Store Code and ID have been moved from E2/E3 to G3/G4 (as you can see in the first screenshot - and also that it is not updating unfortunately)

So I now have:

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("J4") <> "" And Left(Range("J4"), 6) <> "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") <> "Invalid ID" Then
            With rngToFltr
                .AutoFilter Field:=2, Criteria1:=Me.Range("D6")
            End With
        End If

    End If
    
    Application.ScreenUpdating = True

End Sub

Unfortunately, it is still having issue with that same bit of code (as shown in the attached screenshot).

If it can work without the protect sheet code then that is fine. I don't have any other code in place except for what you have given me for this filter, and the two modules for the resets. We tried that Unprotect/Protect in the code because it wasn't working whilst the sheet was Protected.

 

[Alex Blakenburg]

Your text said J3 but your picture and code said J4, I have gone with J4.

Replace all the code you have in the Targets code sheet with the below.
Note: Change the password on the 2nd line to your password.

Private Sub Worksheet_Activate()
    Me.Protect Password:="myPassword", UserInterfaceOnly:=True
End Sub


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") <> "Invalid ID" Then
            With rngToFltr
                .AutoFilter Field:=2, Criteria1:=Me.Range("D6")
            End With
        End If

    End If
    
    Application.ScreenUpdating = True

End Sub

 

[Excel_User_10k]

Apologies, I did mean J4.
Ok I have replaced the code and updated the password.

It was working brilliant at first too. No flickering or issues. Closed the file and reopened it to test again for consistency. Getting same error again. Highlighting the same part of the code on the debug.

 

[Excel_User_10k]

Oh MTD is working when protected but it is still dependant on re-selecting Month between changing stores. But I take if you are trying to get Targets to work first and then will adjust the code for that one after.