My autofilter filtered the wrong criteria

Question

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

You declare a variable named `po_num` but then you do `Criteria1:=po_number`. Notice they are different. Always use `Option Explicit` to avoid this error. Also, I think what you need is `Criteria1:="=" & po_num` — Foxfire And Burns And Burns, Sep 21 '21 at 08:09

score 0 · Answer 1 · answered Sep 21 '21 at 08:14

0

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

answered Sep 21 '21 at 08:14

Ben Mega

502
2
10

Read this [article](https://stackoverflow.com/a/10718179/5514747) firs to avoid select. – Harun24hr Sep 21 '21 at 10:28
1

Yes, I understand that there are better methods of doing what AHMAD is attempting to do; however, po_number was the bug in the code. Avoiding select, ActiveSheet, and hardcoded ranges like "A1:AW500" are best practices but may be difficult for a beginner. From the looks of this code, a simple pivot table may also suffice. – Ben Mega Sep 23 '21 at 01:32

score 0 · Accepted Answer · answered Sep 21 '21 at 10:23

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

My autofilter filtered the wrong criteria

2 Answers2