How do I create a new worksheet for each person in column A, and save it as their name with specific information included in each worksheet?

[Justin_M]

I have a list of names in column A, and each person needs to have their own worksheet created with specific information from their row. How do I create a new worksheet for each person, and save it as their name?

 

[Justin_M]

Apparently there is an error with the row "

sheet_name = Sheets("mySheet").Range("A1:A7").Cells(i, 1).Value

"

Sub AddSheets()

Dim sheets_count As Integer
Dim sheet_name As String
Dim i As Integer

sheets_count = Range("A1:A7").Rows.Count

For i = 1 To sheet_count
  sheet_name = Sheets("mySheet").Range("A1:A7").Cells(i, 1).Value
  Worksheets.Add().Name = sheet_name
Next i

End Sub

 

[Joe4]

sheet_name = Sheets("mySheet").Range("A1:A7").Cells(i, 1).Value

If that truly is the error, see if this works an better:

sheet_name = Sheets("mySheet").Range("A" & i).Value

If it still returns an error, let us know exactly what the error message is, and what the values in column A look like.

I would also change this line:

sheets_count = Range("A1:A7").Rows.Count

to this:

sheets_count = Cells(Rows.Count, "A").End(xlUp).Row

 

[JoeMo]

Here's a simple script that has no error handling capability. Errors can occur if your list includes names already assigned to an existing sheet or names that use characters (like "/", ...) that are not acceptable in sheet names.

Sub Justin()
Dim c As Range
For Each c In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    If Not IsEmpty(c) Then
        Worksheets.Add after:=Sheets(Sheets.Count)
        ActiveSheet.Name = c.Value
    End If
Next c
End Sub