Using VBA macro to filter by variable

Question

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

You are literally filtering on a blank followed by an ampersand, followed by a blank, then a z, etc. — Doug Glancy
– Doug Glancy, Commented Mar 5, 2015 at 20:16
Hi Doug, Oh I apologize, I wasn't sure if it should be just the variable or the " and & used in the variable file path code. Even with just the z the filter code is not working for me. Thank you for clarifying this for me, though! — Michael
– Michael, Commented Mar 5, 2015 at 20:33

Calum · Accepted Answer · 2015-03-05 22:22:56Z

1

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

edited Mar 5, 2015 at 22:22

answered Mar 5, 2015 at 20:14

Calum

2,1302 gold badges22 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Michael Over a year ago

Hi Calum, thanks for the insight. I tried changing Field:=2 to Field:=1 and it now seems to filter on column A instead of B. I have also incorporated Doug's advice and the filter code I have is not working. Thank you for the response!

Calum Over a year ago

after a quick check yes changing Field:= to 1 doesnt quite do what i would have expected, but selecting the whole table works when i try it. I will update the answer to reflect this

Michael Over a year ago

Thanks Calum, unfortunately I am still unable to get the spreadsheet to filter on that variable. Is it possible an issue is arising from trying to filter on a variable from a different spreadsheet?

Michael Over a year ago

z is a variable for the business unit (5 digit number) I hand key in my template. I am able to use this in file paths and tab names in my other spreadsheets. It does utilize an apostrophe before it in order to pull from another spreadsheet. An example is " '54900 ". The data I am filtering on does not have the apostrophe, would that cause any issue?

Calum Over a year ago

@Michael I would expect so. try hard coding the number in to test it, ie Criteria1:=54900, if that works then try using `Criteria1:=Val(z)

|

Collectives™ on Stack Overflow

Using VBA macro to filter by variable

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related