[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 192
  • Last Modified:

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?

0
cookiejest
Asked:
cookiejest
  • 4
  • 2
1 Solution
 
Chris BottomleyCommented:
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
 
Chris BottomleyCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Chris BottomleyCommented:
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
 
Chris BottomleyCommented:
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now