2

the below code works mostly fine. It searches my range in the "list" tab, picks out the first cell (A1), then goes to my "ingredients" tab, filters based on that first cell, and copies and pastes information elsewhere.

The problem arises when it loops back around to look at the 2nd cell (A2) and then filters my list again. it doesn't only filter by A2 but it remembers A1 and filters my list on both criteria! So it becomes cumulative - the 3rd loop filters based on A1, A2, and A3 and so on...

How do it get it to forget the previous lines in the array and only focus on the next line? 

Dim N As Integer
 With Workbooks("file.xlsm").Sheets("list")
    N = .Cells(Rows.Count, "A").End(xlUp).Row
    ReDim ary(1 To N)

    For i = 1 To N
    ary(i) = .Cells(i, 1)


Dim Path As String
Dim filename As String
    Path = "C:\"
filename = Workbooks("file.xlsm").Sheets("list").Cells(i, 1)


Windows("file.xlsm").Activate

Sheets("ingredients").Range("$A$1:$E$1451").AutoFilter Field:=1
Sheets("ingredients").Range("$A$1:$E$1451").AutoFilter Field:=1, Criteria1:=ary, Operator:=xlFilterValues
Range("A1:E1451").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False`
arks
  • 55
  • 8
  • 1
    `ary(i) = .cells(i,1)` is why it is accumulating, try `ary(1) = .cells(i,1)` – Rosetta May 29 '18 at 06:26
  • also.. pasting the complete code might be better.. – Rosetta May 29 '18 at 06:27
  • 7
    Note: Don't use `Integer` for row counting. Excel has more rows than `Integer` can handle. It is recommended [always to use Long instead of Integer](https://stackoverflow.com/a/26409520/3219613) in VBA since there is no benefit in using `Integer` at all. – Pᴇʜ May 29 '18 at 06:29
  • @ Rosetta is right. If you only need to preserve one value, why use array at all? – JvdV May 29 '18 at 06:38
  • @JvdV actually not sure, my research led me to believe that to loop through a list like that I needed an array – arks May 29 '18 at 18:11

0 Answers0