New sheet for each item in column A

[robertdseals]

I have a spreadsheet. I'd like a new sheet for each item in column A The new sheet should be named after that item. (FYI the items will be something like 123456, 987654, etc). I would also like the contents of that row to be pasted into the corresponding sheet. Thoughts?

 

[robertdseals]

PS. I am using Excel 365.

 

[MARK858]

What columns of data are being copied to the new sheet? and where is it being pasted in the new sheet?
What is the name of the sheet with the list?

Also you need to update your profile if you are using 365 now

 

[rowebca]

You can run a loop like this:

Sub copyData()
Sheet1.Activate
For i = 1 To ActiveSheet.Range("a7000").End(xlUp).Row
If ActiveSheet.Cells(i, 1) <> "" Then
    With ThisWorkbook
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = ActiveSheet.Cells(i, 1)
    End With
Sheet1.Activate
ActiveSheet.Rows(i).Select
Selection.Copy
Sheets(CStr(ActiveSheet.Cells(i, 1))).Select
ActiveSheet.Rows(1).Select
Selection.Insert Shift:=xlDown
Sheet1.Activate
End If
Next i
End Sub

Where Sheet1 is the content of column A.

Regards,
GB

 

[rowebca]

...One more thing, I assumed the data in column A is unique.

GB

 

[robertdseals]

You can run a loop like this:

Sub copyData()
Sheet1.Activate
For i = 1 To ActiveSheet.Range("a7000").End(xlUp).Row
If ActiveSheet.Cells(i, 1) <> "" Then
    With ThisWorkbook
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = ActiveSheet.Cells(i, 1)
    End With
Sheet1.Activate
ActiveSheet.Rows(i).Select
Selection.Copy
Sheets(CStr(ActiveSheet.Cells(i, 1))).Select
ActiveSheet.Rows(1).Select
Selection.Insert Shift:=xlDown
Sheet1.Activate
End If
Next i
End Sub

Where Sheet1 is the content of column A.

Regards,
GB

THAT WORKED PERFECT! Thanks!

 

[MARK858]

That's an awful lot of selecting and activating (and no test if the sheet name already exists), below is based on copying the values in columns A-D

Sub robertdseals()
    Dim lastRow As Long, sheetName As String, i As Long
    Application.ScreenUpdating = False
 
    lastRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row

    For i = 2 To lastRow

    sheetName = Sheet1.Cells(i, 1).Text
      
        If Not Evaluate("isref('" & sheetName & "'!A1)") Then
            Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = sheetName
            Worksheets(sheetName).Cells(1, 1).Resize(, 4).Value = Sheet1.Cells(i, 1).Resize(, 4).Value
            ' Worksheets(sheetName).Rows(1).Value = Sheet1.Rows(i).Value ' for entire row
        End If

    Next i

End Sub

 

[rowebca]

That's an awful lot of selecting and activating (and no test if the sheet name already exists), below is based on copying the values in columns A-D

Sub robertdseals()
    Dim lastRow As Long, sheetName As String, i As Long
    Application.ScreenUpdating = False
 
    lastRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row

    For i = 2 To lastRow

    sheetName = Sheet1.Cells(i, 1).Text
      
        If Not Evaluate("isref('" & sheetName & "'!A1)") Then
            Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = sheetName
            Worksheets(sheetName).Cells(1, 1).Resize(, 4).Value = Sheet1.Cells(i, 1).Resize(, 4).Value
        End If

    Next i

End Sub

That was just an example to show him how this works, BTW he was asking for "I would also like the contents of that row to be pasted into the corresponding sheet", your script is not doing that.
Again that was just an example of code to make him see every step, that was my purpose.

Regards,
GB

 

[MARK858]

"I would also like the contents of that row to be pasted into the corresponding sheet", your script is not doing that.


Regards,
GB

In the line in green for entire row, A-D is copied if not using that line (The OP can choose what they want)

 

[rowebca]

It is OK you added after, I am not here for an argument, I want people not just copy/paste code, I want them to understand why, and maybe them to come with a better solution, that is my purpose.

Regards,
GB