Solved!
This is the best code I could come up with. Any suggestions for making it more efficient, less bulky, etc. would be appreciated. Thanks.
Sub CopyRows()
Dim wsh As Worksheet
Dim wsh2 As Worksheet
Dim i As Long
Dim Lr As Long
' Initalize variables
Set wsh = Sheets("Sheet1")
Set wsh2 = Sheets("Sheet2")
With Sheets("Sheet1")
Sheets("Sheet1").Select
' Find the last row of data
Lr = .Range("B2").End(xlDown).Row
' Loop through each row
For i = 2 To Lr
' Decide if to copy based on column C & D
If .Cells(i, "C") = "Master" And .Cells(i, "D") Like "*Active*" Then
.Cells(i, 1).Resize(1, 17).Copy
Sheets("Sheet2").Select
Sheets("Sheet2").Range("B2").Select
ActiveSheet.Paste
End If
If .Cells(i, "C") Like "*Chief Mate*" And .Cells(i, "D") Like "*Active*" Then
.Cells(i, 1).Resize(1, 17).Copy
Sheets("Sheet2").Select
Sheets("Sheet2").Range("B3").Select
ActiveSheet.Paste
End If
If .Cells(i, "C") Like "*Second Mate*" And .Cells(i, "D") Like "*Active*" Then
.Cells(i, 1).Resize(1, 17).Copy
Sheets("Sheet2").Select
Sheets("Sheet2").Range("B4").Select
ActiveSheet.Paste
End If
If .Cells(i, "C") = "Third Mate RO" And .Cells(i, "D") Like "*Active*" Then
.Cells(i, 1).Resize(1, 17).Copy
Sheets("Sheet2").Select
Sheets("Sheet2").Range("B5").Select
ActiveSheet.Paste
End If
If .Cells(i, "C") = "Third Mate" And .Cells(i, "D") Like "*Active*" Then
.Cells(i, 1).Resize(1, 17).Copy
Sheets("Sheet2").Select
Sheets("Sheet2").Range("B6").Select
ActiveSheet.Paste
End If
'sloppy, but, it's the best I could come up with...
Next i
End With
End Sub