pull data from sheet and put it into another sheet if that line is checked off

[robgoldstein]

I have a list of people with other information about that person and I want to get selected people and their columns of information into another sheet. Ideally I would like to have a column that I would put an x on the rows I want in the other sheet.
Is there a way to do this?

 

[Fluff]

How about

Sub RobGoldstein()
    Dim Cl As Range
    
    For Each Cl In Sheets("Roster").Range("A2:A" & Rows.Count).SpecialCells(xlConstants)
        If Evaluate("isref('Team " & Cl.Value & "'!A1)") Then
            Cl.Offset(, 1).Resize(, 5).Copy Sheets("Team " & Cl.Value).Range("A" & Rows.Count).End(xlUp).Offset(1)
        End If
    Next Cl
    MsgBox "Done"
End Sub

 

[alansidman]

Here is the corrected code:

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
        If s1.Range("A" & i) = 1 Then
            lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
            s1.Range("B" & i & ":F" & i).Copy s2.Range("A" & lr2 + 1)
        ElseIf s1.Range("A" & i) = 2 Then
            lr2 = s3.Range("A" & Rows.Count).End(xlUp).Row
            s1.Range("B" & i & ":F" & i).Copy s3.Range("A" & lr2 + 1)
        ElseIf s1.Range("A" & i) = 3 Then
            lr2 = s4.Range("A" & Rows.Count).End(xlUp).Row
            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

 

[robgoldstein]

Thank you so much guys. This is working great. I really appreciate your help.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[alansidman]

Glad we could help & thanks for the feedback

Dittos

 

[robgoldstein]

Fluff. I added a couple of columns and I broke your code. Since I don't understand how your code is working, sadly I haven't been able to fix it. Would you mind helping me one last time please?

Here is a link to my my workbook. https://www.dropbox.com/s/t630m7oufn8ax2m/GTISL Game Sheet test.xlsm?dl=0

 

[Fluff]

Try

For Each Cl In Sheets("Roster").Range("B2:B50").SpecialCells(xlConstants)