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]

Yes, let's get Targets working first. When you reopened the spreadsheet was Targets already the active sheet ?
What happens if you switch to another sheet then back to Targets, does it start working again ?
If it does are you sure you don't have an on Open or on Close event that turns protection on ? Surely you don't manually set protection on every sheet ?

 

[Excel_User_10k]

Ok fair enough. Yes it is. I just test, save, close, re-open and test again. But I was working on a different sheet whilst I was awaiting your reply so it was saved on that one. Now that I reopen it and switch to Targets, it seems to be working perfectly. How is that possible??
I am sure. Only because I don't know what that means haha. So if that is something I need to setup specifically, then I have not done it? Yes I do haha. I have a Sheet do what I need, Protect it and move on to the next. Then Protect the whole Workbook. I tried getting rid of the MTD Calc sheet too as it should just simply be a longer Formula. And I did it and got the correct result. but when I change it to display as a percentage, 80 becomes 8000% -_-. But that is an issue for another day. But at this rate it is going to take me another month haha.

 

[Excel_User_10k]

I take it the same thing would happen on the MTD Sheet? Because that would have been a good Sheet to open up on as, potentially, it would be updated daily.

 

[Alex Blakenburg]

At moment we only have the protection code running when you click switch to the sheet activate.
The code below will but the protection on all sheets but allow VBA to access the sheets.
You can either have that as stand alone macro that you run when you have finished making your changes, or you could put it in the ThisWorkbook module as either a
Workbook_Open() or Workbook_BeforeClose event, but that means it may apply it while you are still working on it an you will need to unprotect the sheet, You will need to decide what is most convenient.
Once you decide you should be able to remove the Worksheet_Activate code since it will just be repeating the below.

Sub ProtectAllSheets()

    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Protect Password:="myPassword", AllowFiltering:=True, UserInterfaceOnly:=True
    Next ws

End Sub

Here is an updated version of the MTD 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

        ' 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") <> "" And 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]

At moment we only have the protection code running when you click switch to the sheet activate.
The code below will but the protection on all sheets but allow VBA to access the sheets.
You can either have that as stand alone macro that you run when you have finished making your changes, or you could put it in the ThisWorkbook module as either a
Workbook_Open() or Workbook_BeforeClose event, but that means it may apply it while you are still working on it an you will need to unprotect the sheet, You will need to decide what is most convenient.
Once you decide you should be able to remove the Worksheet_Activate code since it will just be repeating the below.

Sub ProtectAllSheets()

    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Protect Password:="myPassword", AllowFiltering:=True, UserInterfaceOnly:=True
    Next ws

End Sub

Here is an updated version of the MTD 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

        ' 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") <> "" And 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

Ah right ok. Which is why it isn't working when I am already on that Sheet. So after this change, it won't matter what Sheet the Workbook is closed or opened on, it will work straight away?

When you say I can put it as a stand-alone Macro, that seems useful whilst I am still working on it (as I have already experienced what it is like when trying to work on something and the protection being reapplied at every step) but that does that mean having to assign it to a Button to activate that Macro? The first one definitely seems more convenient but there must be a draw back to that one too or you wouldn't have mentioned the second option?

The only other thing left is that when all 3 reference cells are blank, it shows everything. Which completely defeats the object of having to enter Store ID and Secure ID. When I reset the Cells using the Reset Macro button, it now shows all of it. Before it only did that if D6 and E7 are BOTH blank. Which only happened if a particular code was entered into Store Code and Secure ID. At the minute that is 1, 1 for the purpose of execution, but I will be changing that to something more secure later.

The MTD Code only works when the Sheet is unprotected. I have flicked between Sheets, saved and closed it on another Sheet, opened it again and gone back to MTD Sheet, but every time it still comes up with that same error. Is this because I need to choose one of those other two options first? I thought it was just an amended code from Targets, which DOES work without having to do that.

 

[Alex Blakenburg]

The MTD sheet currently does not have the Worksheet_Activate event in it.
If you use the all sheet protection macro you shouldn't need it.
If you don't use it then you will need to add the Worksheet_Activate event. (exactly the same lines as are in the Targets sheet)

If you put the Protect macro as a stand alone you can either, just hit Alt+F8 to get the list of macros and run it from there, or assign a shortcut key to it or yes have a button.
Worksheet_Open - will run when you open the workbook but it is possible to open the workbook and stop macros running.
Worksheet_BeforeClose - will run when you close the workbook.

If you manually apply the protection you won't be able to set the UserInterface parameter and the macro will fail. In that case your macros would have to unprotect and reprotect the sheet every time a filter changes,

What do you want to happen when the Store fields are blank ?
What do you want to happen when the Month shows "Select..." ?
What about when both of the above happen at the same time ?

 

[Excel_User_10k]

The MTD sheet currently does not have the Worksheet_Activate event in it.
If you use the all sheet protection macro you shouldn't need it.
If you don't use it then you will need to add the Worksheet_Activate event. (exactly the same lines as are in the Targets sheet)

If you put the Protect macro as a stand alone you can either, just hit Alt+F8 to get the list of macros and run it from there, or assign a shortcut key to it or yes have a button.
Worksheet_Open - will run when you open the workbook but it is possible to open the workbook and stop macros running.
Worksheet_BeforeClose - will run when you close the workbook.

If you manually apply the protection you won't be able to set the UserInterface parameter and the macro will fail. In that case your macros would have to unprotect and reprotect the sheet every time a filter changes,

What do you want to happen when the Store fields are blank ?
What do you want to happen when the Month shows "Select..." ?
What about when both of the above happen at the same time ?

Ah right ok, makes sense.

Oh right brilliant! Yeah that is definitely better then. I have removed the Worksheet_Activate coding from the Targets Sheet.

Ok, so the point of the joint codes of Store and Secure ID is that you cannot see ANYTHING without those two codes. We had that before, but now you may as well not bother to enter the Store and Secure ID because the default is that you can see everything haha. The video link below shows what I mean.

20220929_134451.mp4

Shared with Dropbox

www.dropbox.com

So you would enter the Store and Secure ID, that would then reveal the option of the month. If Month says "Select..." then I am fine with that showing the whole year but ONLY for the store entered, not all of it. Then I want the option for an RM to be able to see it all as it shows now but ONLY when a backdoor Store and Secure ID is entered just for that individual. As I said, I currently have this set as 1,1 (which is the only way to make D6 blank and this in turn should clear the filter) but I will be changing both of them to something more secure later. So 1, 1 does not need to be in the code. Just something that defaults the filter to show all when D6 AND E7 is blank. I am having the formula for E7 to be blank when the Month drop down is on Select, or when the Cell for the drop down is blank which i believe can be done by selecting it and pressing delete even when the sheet is protected. The latter probably being better to be honest.

 

[Alex Blakenburg]

In both Targets & MTD just change the last if Statement to the below.
(only the line it red was changed)

        ' 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

 

[Excel_User_10k]

In both Targets & MTD just change the last if Statement to the below.
(only the line it red was changed)

        ' 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

So I have changed that to "Select..."

That stops it from showing everything as default, which is good. But it still doesn't fix the other part. If no Store ID or Secure ID is entered and a month is selected, then it still shows the month. It needs to have a valid store id or secure id before anything else. And there still isn't the option for 'show all' if a store is entered but no month, or when both D6 and E7 are blank to show everything for an RM.

 

[Alex Blakenburg]

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