Alan,
I loved your solution so much I realized I should make this even better. From the roster we pull 3 different teams. Originally I thought I would just run this 3 times. Is it possible to just indicate 1,2 or 3 and put them into sheets Team 1, Team 2, Team 3?
I tried adapting your code but I get an error at after End if
Option Explicit
Sub CpyPst()
Dim s1 As Worksheet, s2 As Worksheet, s3 As Worksheet, s4 As Worksheet
Dim i As Long, lr As Long, lr2 As Long
Set s1 = Sheets("Roster"): Set s2 = Sheets("Team 1"): Set s3 = Sheets("Team 2"): Set s4 = Sheets("Team 3")
lr = s1.Range("B" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = 2 To lr
lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
If s1.Range("A" & i) = "1" Then
s1.Range("B" & i & ":F" & i).Copy s2.Range("A" & lr2 + 1)
If s1.Range("A" & i) = "2" Then
s1.Range("B" & i & ":F" & i).Copy s3.Range("A" & lr2 + 1)
If s1.Range("A" & i) = "3" Then
s1.Range("B" & i & ":F" & i).Copy s4.Range("A" & lr2 + 1)
End If
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "completed"
End Sub
I don't know if anything I changed would work or not.
Thanks so much for your help.