Filters + Varibles + Vba

[Akihitoc]

I have sheet 1 with a data and I have sheet 2 with variables
I would like filter sheet 1 using variables from sheet 2 (showing all variables at same time on the filter)

Thanks!

 

[My Aswer Is This]

1. You did not tell us what the variables are!
2. You did not tell us where on sheet 2 the variables are!
3. You did not tell us what fields we are going to filter?
4. You did not tell us where on Sheet (1) the data is located.

About all you told us was I have data on sheet (1) and filters on sheet (2)

We need more details.

 

[Akihitoc]

Sorry for that, ok, well Im using Excel 2013

I have 3 sheets
The 1st is "WIRES", the 2nd shee is "BOM" and the 3rd is "Sheet 1" (on sheet1 I have all the variables, I have around 8 variables with 2 letters, for examlpe WA and TD)

So I wanted that the list on "WIRES" and the on the sheet "BOM" to show me only those results that start with WA and also that start with TD
What I recorded is that I copied the variable WA and the pasted on the filter, and the the same with TD (selecting "add the current selection the filter")
The colum to filter on "WIRES" sheet is column D and on "BOM" is column C
Here is the code that I recorded
Hopefully this would be helpful
Thanks!


' Keyboard Shortcut: Ctrl+Shift+X
'
Windows("WIRES and BOM.xlsx").Activate
Sheets("Sheet1").Select
Range("G8").Select
Selection.Copy
Sheets("WIRES").Select
ActiveSheet.Range("$A$5:$Z$863").AutoFilter Field:=4, Criteria1:=Array( _
"WA10-GA", "WA10-GJ", "WA10-GK", "WA10WH1", "WA10WH5", "WA77-G", "WA92A16", "WA92B16", _
"WA92BA16", "WA92C16", "WA92D16", "WA95-G", "WA95-GA", "WA95-GB"), Operator:= _
xlFilterValues
Sheets("BOM").Select
ActiveSheet.Range("$A$3:$N$1818").AutoFilter Field:=3, Criteria1:="WA"
Sheets("Sheet1").Select
Range("H8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("WIRES").Select
ActiveSheet.Range("$A$5:$Z$863").AutoFilter Field:=4, Criteria1:=Array( _
"TA TD47#140", "TA TD47#160", "TD92#101", "TD92#105", "TD92#109", "TD92#110", _
"TD92#111", "TD92#116", "TD92#118", "TD92#122", "TD92#123", "TD92#130", "TD92#133", _
"TD92#134", "TD92#136", "TD92#137", "TD92#141", "TD92#143", "TD92#151", "TD92#152", _
"TD92#154", "TD92#155", "TD92#157", "TD92#162", "TD92#163", "TD92#164", "TD92#165", _
"TD92#169", "TD92#170", "TD92#171", "TD92#174", "TD92#176", "TD92#177", "TD92A170", _
"TD92-G", "WA10-GA", "WA10-GJ", "WA10-GK", "WA10WH1", "WA10WH5", "WA77-G", "WA92A16", _
"WA92B16", "WA92BA16", "WA92C16", "WA92D16", "WA95-G", "WA95-GA", "WA95-GB"), _
Operator:=xlFilterValues
Sheets("BOM").Select
ActiveSheet.Range("$A$3:$N$1818").AutoFilter Field:=3, Criteria1:="=TD", _
Operator:=xlOr, Criteria2:="=WA"
End Sub

 

[My Aswer Is This]

Thanks for giving more details. This is something I think someone else here at Mr. Excel will be able to give you a better answer then I can. Check back in here in about 24 hrs. and I believe someone will have a answer for you. I will continue to monitor this thread and if you get no answers I will see what I can do.

 

[skywriter]

Maybe this.

Sub Akihitoc()
    With Worksheets("WIRES").Range("A1").CurrentRegion
        .AutoFilter 4, "TD*", 2, "WA*"
    End With
    
    With Worksheets("BOM").Range("A1").CurrentRegion
        .AutoFilter 3, "TD*", 2, "WA*"
    End With
End Sub

 

[Akihitoc]

thanks man!
It works, but....

What if the variable is not "WA" or "TD", I mean I need a generic formula to use, the variables are alwas on the same place on "Sheet1" cell "G8", "H8", "I8", etc to until to "S8" sometimes are less variables.
Hope some can help
Thanks again!!

 

[skywriter]

Okay this assumes the items you want to filter by are in row 8 starting in G8 and go right in row 8. It assumes they look just like the numbers you want to filter, TD92#136 for example.

Sub Akihitoc()
Dim arr

    With Worksheets("Sheet1")
        arr = .Range(.Cells(8, 7), .Cells(8, .Columns.Count).End(xlToLeft)).Value
    End With
    
    With Worksheets("WIRES").Range("A1").CurrentRegion
        .AutoFilter 4, arr, 7
        .AutoFilter
    End With
    
    With Worksheets("BOM").Range("A1").CurrentRegion
        .AutoFilter 3, arr, 7
    End With
End Sub

 

[Akihitoc]

Thanks a lot!
I really appreciate it

It worked with the "BOM" sheet because the variable is exacly the same on the filter, but it didn't work with the "WIRES" sheet because the variable WA is part of the words on the colum filtered, the list is like WAG10, WA12C, WA34D, etc.

 

[skywriter]

My pleasure, thanks for the feedback.