VBA Multiple If then statement help

[jdb2313]

Hello,
I am trying to modify this vba code that would find rows containing 20 products and cut/paste them onto another sheet. I tried to add an elseif to this code but it is not working. I am new to this so any help would be appreciated.


Sub CopyYes()
Dim c As Range
Dim j As Integer
Dim Source As Worksheet
Dim Target As Worksheet


' Change worksheet designations as needed
Set Source = ActiveWorkbook.Worksheets("Sheet1")
Set Target = ActiveWorkbook.Worksheets("Sheet2")


j = 1 ' Start copying to row 1 in target sheet
For Each c In Source.Range("a1:a1000000") ' Do 1000 rows
If c = "Product1" Then
Source.Rows(c.Row).Copy Target.Rows(j)
j = j + 1

End If
Next c
End Sub

 

[mumps]

Do you have a list of the 20 products somewhere in your workbook, perhaps on another sheet? If so, what is the name of that sheet and in which range are the products located? For example, Sheet2, A1:A20.

 

[jdb2313]

Yes, it is on sheet2 a2:a20

 

[mumps]

This macro assumes that you have headers in row 1 and the data starts in row 2. Since you have your product list in column A of Sheet2, the data will be copied starting at row 21 of Sheet2.

Sub CopyYes()
   Dim Ary As Variant
   With Sheets("Sheet2")
      Ary = Application.Transpose(.Range("A2:A20").Value)
   End With
   With Sheets("Sheet1").Cells(1).CurrentRegion
      .AutoFilter 1, Ary, xlFilterValues
      Sheets("Sheet1").AutoFilter.Range.Offset(1).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
      Sheets("Sheet1").Cells(1).AutoFilter
   End With
End Sub

 

[jdb2313]

Wow Awesome.. Thank you so much for you help!!!