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.Try this:
VBA Code: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
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.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.
But where is the list?The data starts in cell a1 in a sheet called "name_list".
Help us help you, you must provide all relevant information, otherwise we will be guessing what you need.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.
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
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.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.
PERFECT! ThanksBut 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
VBA Code: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.
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.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
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 againTry:
VBA Code: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