Macro to create new sheets in a workbook based on a name list

[Hart]

I have a list of names on a sheet in a workbook. Using the list, I would like to create a new sheet for each name in the list and name the new sheet based on the name. The new sheets would be added "BEFORE" the currently selected sheet. Thanks.

 

[DanteAmor]

Try this:

Sub CreateSheets()
  Dim c As Range
  
  For Each c In Range("C2", Range("C" & Rows.Count).End(3))
    If c.Value <> "" Then
      Sheets.Add(ActiveSheet).Name = c.Value
    End If
  Next
End Sub

 

[Hart]

Try this:

Sub CreateSheets()
  Dim c As Range
 
  For Each c In Range("C2", Range("C" & Rows.Count).End(3))
    If c.Value <> "" Then
      Sheets.Add(ActiveSheet).Name = c.Value
    End If
  Next
End Sub

This works for the first name in the list but then does not continue to the second and subsequent names. Sorry, but I also forgot to mention that the macro should copy a sheet called "Name Template" renaming it to the name currently being copied.

 

[DanteAmor]

If you provide us with more data, that would be great, that way I could adjust the macro. For example, which column is your data in. In which row the data begins.

And it would be wonderful if you provided an image to see how your data is in the cells.

 

[Hart]

If you provide us with more data, that would be great, that way I could adjust the macro. For example, which column is your data in. In which row the data begins.

And it would be wonderful if you provided an image to see how your data is in the cells.

The data starts in cell a1 in a sheet called "name_list". The sheet to be copied and renamed is called "Name Template". I hope this gives you the info you need. Thanks very much.

 

[DanteAmor]

The data starts in cell a1 in a sheet called "name_list".

But where is the list?
The first name in cell A1, the second name in cell A2, the third name in A3 and so on down?

Sorry, but I also forgot to mention that the macro should copy a sheet called "Name Template" renaming it to the name currently being copied.

Help us help you, you must provide all relevant information, otherwise we will be guessing what you need.


Another attempt

Sub CreateSheets()
  Dim c As Range
  
  With Sheets("name_list")
    For Each c In .Range("A1", .Range("A" & Rows.Count).End(3))
      If c.Value <> "" Then
        Sheets("Name Template").Copy before:=Sheets(.Name)
        ActiveSheet.Name = c.Value
      End If
    Next
  End With
End Sub

Try and comment.

 

[Hart]

The data starts in cell a1 in a sheet called "name_list". The sheet to be copied and renamed is called "Name Template". I hope this gives you the info you need. Thanks very much.

The name data starts in cell a1 in a sheet called "name list". Typically, there are 15-30 names and a sheet called "name template" needs to copied and renamed for each of the names in the list.

But where is the list?
The first name in cell A1, the second name in cell A2, the third name in A3 and so on down?


Help us help you, you must provide all relevant information, otherwise we will be guessing what you need.


Another attempt

Sub CreateSheets()
  Dim c As Range
 
  With Sheets("name_list")
    For Each c In .Range("A1", .Range("A" & Rows.Count).End(3))
      If c.Value <> "" Then
        Sheets("Name Template").Copy before:=Sheets(.Name)
        ActiveSheet.Name = c.Value
      End If
    Next
  End With
End Sub

Try and comment.

PERFECT! Thanks

 

[Hart]

The name data starts in cell a1 in a sheet called "name list". Typically, there are 15-30 names and a sheet called "name template" needs to copied and renamed for each of the names in the list.

PERFECT! Thanks

I have one more "tweak" to request. I would like the contents of cell b1 ( or b2 etc. as it proceeds through the names) to be copied to cell L399 of the new sheet.
EG:
A B
1 Hart Leppard 123-456-789 (123-456-789 copied to cell L399 in the new Hart Leppard sheet)
2 Joe Blow 987-654-321 (987-654-321 copied to cell L399 in the new Joe Blow sheet)

 

[DanteAmor]

Try:

Sub CreateSheets()
  Dim c As Range
  
  With Sheets("name_list")
    For Each c In .Range("A1", .Range("A" & Rows.Count).End(3))
      If c.Value <> "" Then
        Sheets("Name Template").Copy before:=Sheets(.Name)
        ActiveSheet.Name = c.Value
        range("L399").value = c.offset(0,1).value
      End If
    Next
  End With
End Sub

 

[Hart]

Try:

Sub CreateSheets()
  Dim c As Range
 
  With Sheets("name_list")
    For Each c In .Range("A1", .Range("A" & Rows.Count).End(3))
      If c.Value <> "" Then
        Sheets("Name Template").Copy before:=Sheets(.Name)
        ActiveSheet.Name = c.Value
        range("L399").value = c.offset(0,1).value
      End If
    Next
  End With
End Sub

Works great, thanks again