Help with Macro to Copy/Paste cells to different worksheet based on Value

Hi BehrBrew

Will the Data in Master ALWAYS be new Data (Data that's not been previously transfered)?

Hi BehrBrew

I've added two Modules to your Sample Workbook and an additional Worksheet named "Template".

In order for the Code in Module Transfer to function properly the Formulas in the Individual Player sheets in Range("B3:F33") need to be cleared. This Code does that

Option Explicit

Sub Clear_Stuff()
    Dim ws As Worksheet
    Dim rng As Range

    For Each ws In ThisWorkbook.Worksheets
        If Not ws.Name = "MASTER" And Not ws.Name = "HIGH" And Not ws.Name = "AVERAGE" _
                And Not ws.Name = "LOW" And Not ws.Name = "Template" Then
            With ws
                Set rng = .Range("B3:F33")
                rng.ClearContents
            End With
        End If
    Next ws
End Sub

The Code in Module Transfer is the Code that distributes the Master Records to the Individual Player sheets. The Code will add a new Worksheet for any new Player if the Individual Player sheet does not already exist. You'll receive a message that the New Player Sheet has been added. This Code runs from a Button on Master sheet.

Option Explicit

Sub Transfer_Data()
    Dim ws As Worksheet
    Dim rng As Range, cel As Range, myCell As String
    Dim LR As Long
    Dim NR1 As Long

    Set ws = Sheets("Master")
    With ws
        LR = .Cells(5, 2).End(xlDown).Row
        For Each cel In .Range("B5:B" & LR)
            
             If Not WorksheetExists(cel.Text) Then
                Worksheets.Add(Before:=Worksheets("HIGH")).Name = cel.Text
                Sheets("Template").Cells.Copy
                Sheets(cel.Text).Range("A1").PasteSpecial
                Application.CutCopyMode = False
                
                MsgBox cel.Text & " has been added"
                
                Sheets(cel.Text).Range("B1").Value = cel.Text
             End If
                With Sheets(cel.Text)
                    Set rng = .Range("B2", .Cells(.Rows.Count, "B").End(xlUp))
                    myCell = rng.Find("Last", , xlValues, xlWhole, xlByRows, xlNext, False).Offset(-2, 0).Address

                    NR1 = .Range(myCell).End(xlUp).Row + 1
                    .Cells(NR1, "B").Resize(1, 5).Value = cel.Offset(0, 1).Resize(1, 5).Value

                End With
        Next cel
    End With
End Sub


Function WorksheetExists(SheetName As String, _
        Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
    Dim WB As Workbook
    Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
    On Error Resume Next
    WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)
End Function

I've not monkeyed with Cells B34 to G39 in the Individual Player Sheets...only you know what these are to do. Of course, try the Code n the Sample file and/or a COPY of your live file. Let me know of issues.

You're welcome...glad I could help.

I ran this macro for one name but need to do it for different names - so I need to get the macro to use the name I run in the cell I'm in when I run it:

Sub CopyDataForName()
'
' CopyDataForName Macro
'
' Keyboard Shortcut: Ctrl+Shift+Y
'
    Sheets("2B 2014 Proposed Class List").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Andrew Dibby"
    Sheets("1B 2013 data").Select
    Cells.Find(What:="Andrew Dibby", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Range("A1:Y1").Select
    Selection.Copy
    Sheets("2B 2014 Proposed Class List").Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveSheet.Paste
End Sub

MaryCW,

Welcome to the forum.

2 pointers - 1. I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE] before your code and [/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.

2. Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.