My autofilter filtered the wrong criteria-CodePudding

I'm very beginner in VBA and i have an autofilter macro that will filter based on the user input. A new spreadsheet will be generated and the filtered data will be paste on the new spreadsheet.

But the problem that i have is like so :

-------------------------------------------------
Example of data:

Name     Model       PO Number
Kevin    Winon       3000     
Billy    Kent        3001
Esther   Kent        3000
Tom      Master      3005

For example if the filtered criteria = 3000

Output receive is:
 
Name     Model       PO Number     
Billy    Kent        3001
Tom      Master      3005

Desired output

Name     Model       PO Number
Kevin    Winon       3000     
Esther   Kent        3000
---------------------------------------------------------------
My Code :

Sub po_finder()
Dim po_num As String

Sheets("Analysis").Unprotect "mech_eng_123"
po_num = UserForm_PO.TextBox1.Value

Sheets("Analysis").Select
ActiveSheet.Range("W1").AutoFilter Field:=23, Criteria1:=po_number
ActiveSheet.Range("A1:AW500").Select
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Analysis").AutoFilterMode = False

End Sub

CodePudding user response：

It appears the issue is with the "po_number" reference when you filter your data. Previously, you refer to that variable as po_num. Try this:

Sub po_finder()
Dim po_num As String

Sheets("Analysis").Unprotect "mech_eng_123"
po_num = UserForm_PO.TextBox1.Value

Sheets("Analysis").Select
ActiveSheet.Range("W1").AutoFilter Field:=23, Criteria1:=po_num
ActiveSheet.Range("A1:AW500").Select
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Analysis").AutoFilterMode = False

End Sub

CodePudding user response：

Your criteria is being applied to column AS because in ActiveSheet.Range("W1").AutoFilter Field:=23, Criteria1:=po_number Field=1 would be column W. The criteria is "" because the value 3000 is in po_num not po_number.

Sub po_finder()
    Dim po_num As String, ws As Worksheet, wsNew As Worksheet

    Set ws = Sheets("Analysis")
    ws.Unprotect "mech_eng_123"
    po_num = UserForm_PO.TextBox1.Value
    
    Set wsNew = Sheets.Add(After:=ws)
    With ws.Range("A1:AW500")
         .AutoFilter Field:=23, Criteria1:=po_num
         .Copy wsNew.Range("A1")
    End With
    ws.AutoFilterMode = False
    MsgBox "Data copied to " & wsNew.Name & " " & wsNew.UsedRange.Address, vbInformation

End Sub