Loop through Range and copy rows based on multiple criteria

[JohChris01]

Hello,

I have a worksheet that I am copy rows to multiple worksheets based on multiple criteria. I started with the code to copy to one worksheet (Mens) from the master data sheet (Formatted Data). I am trying to use an If statement within a loop.

1. In Column A to copy only if criteria matches 10, 15, or 17 and
2. In column B copy row only if criteria does NOT match string "By Season" or "By Divisi".

The first set of criteria works just find but I can not get it to exclude the rows with the second criteria

Sub PasteDataToWorksheets()'
'Shortcut Key = Cntrl + Shift + W
'


    Dim LastRow As Long
    Dim i As Long, j As Long
   
    
       
   'Find the last used row in a Column: column A in this example
   With Worksheets("Formatted Data")
      LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
   End With


   'first row number where you need to paste values in Mens'
   With Worksheets("Mens")
      j = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
   End With


   For i = 1 To LastRow
       With Worksheets("Formatted Data")
           If .Cells(i, 1).Value = 10 Or .Cells(i, 1).Value = 15 Or .Cells(i, 1).Value = 17 Then
           If .Cells(i, 2).Value <> "BY SEASON" Or .Cells(i, 2).Value <> "BY DIVISI" Then
               .Rows(i).Copy Destination:=Worksheets("Mens").Range("A" & j)
               j = j + 1
           End If
           End If
       End With
   Next i


    MsgBox "Macro is Complete"




End Sub

[Charlize]

If you want to copy the rows when i,2 <> BY SEASON / BY DIVISI , I think you need to use AND instead of OR.
AND will say copy everything but not when one of those two is present

OR will say copy everything but not if BY SEASON OR BY DIVISI
so if it's BY SEASON but not BY DIVISI the code will copy anyway because BY DIVISI isn't present

What if you say = "BY SEASON" or = "BY DIVISI" you add a else part with the copy routine en let the then part blank.

Charlize