CODEWITHSUNDEEP
vba
Michael
Michael

Reputation: 87

Using VBA macro to filter by variable

I have set up a template to pull data from another spreadsheet by filtering said spreadsheet on a variable established in my template.

I can not seem to get the macro to properly filter on my variable (it keeps filtering so that no data remains visible). The spreadsheet I am pulling from goes from column A to AM and has 20830 rows.

I need to utilize my variable to filter column B. Column B is currently set up as a VLOOKUP and I have tried paste valuing the column but to no avail. Any insight as to how I can accomplish this would be greatly appreciated! Please let me know if I need to specify anything more.

Below I have provided my current VBA script (any advice on improving it would also be appreciated). Again, thank you so much for your help!! (Variable to filter on is z, established in an earlier portion of the script)

'Below will grab the Holdings data

Workbooks.Open Filename:= _
    "S:\Cashinvt\Audits\D&T" & v & "\PAM.allhold" & y & ".w.stat.xlsm", UpdateLinks:=0

    Sheets("allhold").Select
'Here is where I start questioning my code, filtering on variable z

Columns("B:B").Select

ActiveSheet.Range("$B$1:$B$22001").AutoFilter Field:=2, Criteria1:="z"

'Here I am trying to copy the newly filtered data and paste it into another spreadsheet

Range("B1:AM20831").Select

Selection.SpecialCells(xlCellTypeVisible).Select

Selection.Copy

Windows("Audit.Support.Template.xlsm").Activate

Sheets("Holdings").Select

Range("A1").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Windows("S:\Cashinvt\Audits\D&T" & v & "\PAM.allhold" & y & ".w.stat.xlsm").Activate

Sheets("allhold").Select

Windows("S:\Cashinvt\Audits\D&T" & v & "\PAM.allhold" & y & ".w.stat.xlsm").Activate
ActiveWorkbook.Close SaveChanges:=False
Application.DisplayAlerts = True

Sheets("Cover Page").Select
Range("K1").Select

End Sub

Upvotes: 1

Views: 6533

Answers (1)

Calum
Calum

Reputation: 2130

ActiveSheet.Range("$B$1:$B$22001").AutoFilter Field:=2, Criteria1:=" & z & "

You are giving a range of only one column but stating Field:=2 try giving the range as the full table, ie

ActiveSheet.Range("$A$1:$AM$22001").AutoFilter Field:=2, Criteria1:=CInt(z)

Notes: i have also included Dougs comment regarding the criteria

Upvotes: 1

Related Questions