Advanced Filtering Data Pulled Form Multiple Worksheets And Displayed On One Sheet

Hello,

First off I dont know much of anything when it comes to programming in Excel with VBA, or programming in general. Ive spent a couple days in my free time trying to gather, search and tinker around with making things work with limited knowledge the way I need it to work. So excuse my poor coding for all you Expert Coders.

The attached file was originally from someone else, but I need more out of it. I added Base_3 and 4 tabs and messed around with the coding some to get close to what I want. I also added the Base column in the tables and put the value number in the column matching the tab name value.

So I will try to explain what I need.
When I am working in Excel, similar to what is seen in the example file, I start out with one tab/worksheet named "Base_1". As I am working, I will create new Tabs and rename them "Base_2 and so on". while I am working and at the end of my work, I would like to use a Data Filtering Table as exampled in the file sheet name "Result". I would like the Filtering table to filter out a name or base value when displayed in the filter options. I could have up to 20 Tabs/worksheets by the end of my work or more or less.

- If I want to filter out a specific Base value say 2, then I want A) the table to display all the data form worksheet Base_2 with no name filtered and B) with a name filtered.
- If I dont filter out a specific Base or Name, then I want the filter table to display all data from all worksheets
- sometimes not all my tab names will be names exactly "Base_1 or Base_2" and so on, sometimes I may add more text such as "Base_3 NW" or "Base_3 SWNW". So if it is possible to have the macro to search for tabs containing Base_# with added wildcard text, then that would be great, if not then I can live with naming a Sheet/tab name exactly.

What I have now:

Option Explicit
Sub Filter()
    Dim ws As Worksheet
        
    For Each ws In ThisWorkbook.Sheets

         If Sheets("Result").Range("G2") = "" Then 'this if statement is saying, if no base filtering option is selected, then all data in base tabs will display
    
            If InStr(1, ws.Name, "BASE_1") Then
        
                Sheets("BASE_1").Range("A1:F1000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("B1:B2"), CopyToRange:=Range("B4:G4"), Unique:=False
        
                ElseIf InStr(1, ws.Name, "BASE_2") Then 'keep adding this bit of code from elseif to end with for however many more tabs might be created in the future, just change the sheet name in the code
        
                    With Sheets("BASE_2").Range("A1:F1000")
                         .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("B1:B2"), Unique:=False
                         .Resize(.Rows.Count - 1).Offset(1).Copy Range("B4:G4").Offset(Range("B4:G4").CurrentRegion.Rows.Count)
                        'the code ".WorkSheet.ShowAlldata" will not allow Result tab to show all data from all worksheets with no name filtered, removing it works but when filtering a name the base tabs/sheets get filtered aswell
                    End With
            
                 ElseIf InStr(1, ws.Name, "BASE_3") Then
        
                    With Sheets("BASE_3").Range("A1:F1000")
                        .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("B1:B2"), Unique:=False
                        .Resize(.Rows.Count - 1).Offset(1).Copy Range("B4:G4").Offset(Range("B4:G4").CurrentRegion.Rows.Count)
                    
                    End With
            
                 ElseIf InStr(1, ws.Name, "BASE_4") Then
        
                    With Sheets("BASE_4").Range("A1:F1000")
                        .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("B1:B2"), Unique:=False
                        .Resize(.Rows.Count - 1).Offset(1).Copy Range("B4:G4").Offset(Range("B4:G4").CurrentRegion.Rows.Count)
                    
                    End With
                
             End If
             
            End If
    
        
    
         If Sheets("Result").Range("G2") = "1" Then

            Sheets("BASE_1").Range("A1:F1000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("B1:B2"), CopyToRange:=Range("B4:G4"), Unique:=False
        
        End If
        
        If Sheets("Result").Range("G2") = "5" Then

            Sheets("BASE_5").Range("A1:F1000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("B1:B2"), CopyToRange:=Range("B4:G4"), Unique:=False
        
        End If
        
    Next
        
End Sub

Let me know if you need more explanation from me.

Again thanks to those viewing this and helping out, much appreciation.

-Brent