Copying/Pasting data to tab sharing same name

[ManhattanJM]

Hi everyone,

Just having a bit of difficulty with the last two steps of a macro I'm trying to piece together. I have the below data and I'm trying to organize the data in four steps.

1. Create new tabs for each name

2. Copy and paste the header to each tab

3. Copy and paste the rows for each player to their corresponding tab

4. Add a footer row in each tab that will take the sum total of the data

I've got one and two done based on the below code, but having difficulty getting the player details to the corresponding tab and the sum footer row. For example, I already have a tab named Russell Westbrook, but now I want to take rows 2,3,4,5, and 6 and paste them from Sheet1 to the Russell Westbrook sheet. If anyone could help that would be excellent! Thank you

1.)
Sub AddSheets()
Dim xRg As Excel.Range
Dim wSh As Excel.Worksheet
Dim wBk As Excel.Workbook
Set wSh = ActiveSheet
Set wBk = ActiveWorkbook
Application.ScreenUpdating = False
For Each xRg In wSh.Range("A1:A58")
With wBk
.Sheets.Add after:=.Sheets(.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = xRg.Value
If Err.Number = 1004 Then
Debug.Print xRg.Value & " already used as a sheet name"
End If
On Error GoTo 0
End With
Next xRg
Application.ScreenUpdating = True
End Sub

2.)
Sub CopyHeader()
For Each wsSheet In ThisWorkbook.Worksheets
wsSheet.Rows(1).Value = Worksheets("Sheet1").Rows(1).Value
Next wsSheet
End Sub

 

[mikerickson]

Re: 4) You might want to have your Totals row at the top (in row 2) with formulas like =SUM(B3:B99999). That way you can add data to the sheet and not have to use Insert to keep the totals correct. Plus, it puts the Totals data in the same cells on every sheet. Makes it easier for formulas / macros to find the totals row.

Re: 3)

Dim oneCell as Range

With Sheets("masterSheet")
    For Each oneCell in Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlup))
        With oneCell
            Sheets(.Value).Cells(Rows.Count, 1).End(xlUp).Offset(1,0).Resize((1, 8).Value = oneCell.Resize(1, 8).Value
        End With
    Next oneCell
End With

 

[ManhattanJM]

Thank you very much for your time!

That's a great idea about the sum rows.

Apologies, it's been a while since I last used VBA. I'm getting a "compile error : syntax error" on the below

 

[mikerickson]

There should only be one left parenthesis after the Resize