title: Using VBA macro to filter by variable
body: <p>I have set up a template to pull data from another spreadsheet by filtering said spreadsheet on a variable established in my template.  </p>
  
  <p>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. </p>
  
  <p>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. </p>
  
  <p>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)</p>
  
  <pre><code>'Below will grab the Holdings data
  
  Workbooks.Open Filename:= _
      "S:\Cashinvt\Audits\D&amp;T" &amp; v &amp; "\PAM.allhold" &amp; y &amp; ".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&amp;T" &amp; v &amp; "\PAM.allhold" &amp; y &amp; ".w.stat.xlsm").Activate
  
  Sheets("allhold").Select
  
  Windows("S:\Cashinvt\Audits\D&amp;T" &amp; v &amp; "\PAM.allhold" &amp; y &amp; ".w.stat.xlsm").Activate
  ActiveWorkbook.Close SaveChanges:=False
  Application.DisplayAlerts = True
  
  Sheets("Cover Page").Select
  Range("K1").Select
  </code></pre>
  
  <p>End Sub</p>