Copy a sheet rename it from a list and add values in some cells

I need a macro in excel that copys a sheet called "template" for each value in a list.

each new sheet should then be named by the value in the list AND a cell within the sheet should have the name copied to it.

It sounds likea  simple task, any help please?

cookiejestAsked:
Who is Participating?
 
Chris BottomleySoftware Quality Lead EngineerCommented:
In that case:

Chris
Sub nuSheets()
Dim sh As Worksheet

For Each Nam In Sheets("Tracking Sheet").Range("a2:a45").Cells
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    With Sheets(Sheets.Count)
        .Name = Nam.Text
        .Range("a1").Value = Nam.Text
    End With
Next

End Sub

Open in new window

0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
SOmething like the following which is code in the specific workbook:

Chris
Sub nuSheets()
Dim sh As Worksheet

For Each nam In Array("fred", "doris")
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    With Sheets(Sheets.Count)
        .Name = nam
        .Range("a1").Value = Name
    End With
Next

End Sub

Open in new window

0
 
cookiejestAuthor Commented:
Ok that is almost what I need, can you explain where I would input the array A2:A45 within my sheet called "Tracking Sheet".

I assume a1 is the cell where the sheet name is pasted within it.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Chris BottomleySoftware Quality Lead EngineerCommented:
The sheet copy takes all the data from template ... Is your array a vba array that needs to be placed in the copy and if so how is it defined?

Yes a1 is the cell with the sheet name ... Albeit a typo fixed below.

Let me know re tha array and I will modify accordingly

Chris
Sub nuSheets()
Dim sh As Worksheet

For Each nam In Array("fred", "doris")
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    With Sheets(Sheets.Count)
        .Name = nam
        .Range("a1").Value = Nam
    End With
Next

End Sub

Open in new window

0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Re-reading it looks as though the 'array' to be copied is the range a2:a45 of sheet tracking data therefore:

Chris
Sub nuSheets()
Dim sh As Worksheet

For Each Nam In Array("fred", "doris")
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    With Sheets(Sheets.Count)
        .Name = Nam
        .Range("a1").Value = Nam
        Sheets("Tracking Sheet").Range("a2:a45").Copy .Range("a2:a45")
    End With
Next

End Sub

Open in new window

0
 
cookiejestAuthor Commented:
No each item in the array (which is in cells a2:a45) needs a sheet that is based on the "template". The name of the sheet is the name of the item in the array. The same value also needs to be placed within a cell in its newly created sheet
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.