Thread: Copy paste entire row between worksheets

Hello all,
On a recurring basis, I have an extract with text data with an unknown number of rows that are mutually exclusive to any other row. I need to do a couple things with the data.

1. Create a new worksheet for each row in Column A and rename the worksheet e.g "1. Doe D" will be the first sheet. I found a macro on this site that does this successfully.

Here's the macro:

Sub addsheets()
On Error GoTo sheet_repeat

Dim sheet_range As Range

sr_count = Selection.Rows.Count
Set sheet_range = Selection
For i = 1 To sr_count
    ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
    Sheets(Worksheets.Count).Name = sheet_range(i)
    
Next i
Exit Sub
sheet_repeat:
MsgBox ("Sheet Number " & i & " of your range is a repeat name")
Sheets(Worksheets.Count - i).Select
End Sub

2. I need the entire row for Row 2 in worksheet "Student" to be pasted in the in the "1. Doe D" (This is the row the new worksheet was created for).


I've been trying to create a macro that would literally go to the first entry create the new worksheet, rename it based on Column A, copy the entire row in the worksheet "Student", and then paste the row in the new worksheet.

All attempts to loop and using For Next statements have failed. Any thoughts?

For reference, I've attached an example of the file.
Copy Paste question.xls

You had your code in the WorkBook Module, it needs to be in a regular module.
Try this

Sub Button1_Click()
    Dim r As Range
    Dim c As Range
    Set r = Range("A2", Range("A65536").End(xlUp))

    For Each c In r.Cells

        ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
        Sheets(Worksheets.Count).Name = c
        c.EntireRow.Copy Destination:=Range("A1")

    Next c

End Sub

Here is your sample back