Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Create & name worksheets from a list and copy data [Solved/Closed]

Report
Posts
3
Registration date
Tuesday October 4, 2016
Status
Member
Last seen
October 4, 2016
 -
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
 -
Hi Guys

Really new to this and thanks to previous posts on here I have managed to create code to complete the first part of my jigsaw, just struggling with the copy data element.

My workbook contains two sheets 1) 'Data Entry' which has two columns "Room Name" & "Level" and 2) 'Template' which is copied and renamed for each "Room Name" in the list when the code runs

The following code enables me to create individual worksheets, however I would like to be able to Insert the "Room Name" & "Level" on each sheet which is created and would be ever so grateful for any assistance;

Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("Data Entry").Range("A2")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

For Each MyCell In MyRange
Sheets("Template").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Next MyCell
End Sub

This is a link to the Room sheet file: https://www.dropbox.com/s/xnn3omun0uhil26/Room%20Sheet.xlsx?dl=0
Related:

2 replies

Answer 1 / 2
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
 458
Hi RachJol,

You mean like this?: 
Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("Data Entry").Range("A2")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

For Each MyCell In MyRange
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
    Range("F3").Value = MyCell.Value
    Range("F4").Value = MyCell.Offset(0, 1).Value
Next MyCell
End Sub


Best regards,
Trowa
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
Report
Posts
3
Registration date
Tuesday October 4, 2016
Status
Member
Last seen
October 4, 2016
Hi Trowa

Thanks so much, that works brilliantly. The only thing is when it reaches the end of the list it tries to copy 'Template' again and comes up with a Run-time error 1004 "That name is already taken. Try a different one".

If you can suggest anything that would be very helpful.

Thanks
Rachael
Report
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
 458 >
Posts
3
Registration date
Tuesday October 4, 2016
Status
Member
Last seen
October 4, 2016
Hi Rachael,

That would suggest a duplicate entry in your Room name list.

I downloaded your file, run the code and no error occured.

What did you change?

Best regards,
Trowa
Report
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
 458
Also, how about hiding your template sheet and adding the Visible statement in your code for a cleaner look: 
Sub CreateSheetsFromAList()
Dim MyCell, MyRange As Range

Set MyRange = Sheets("Data Entry").Range("A2")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

Sheets("Template").Visible = True

For Each MyCell In MyRange
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = MyCell.Value ' renames the new worksheet
    Range("F3").Value = MyCell.Value
    Range("F4").Value = MyCell.Offset(0, 1).Value
Next MyCell

Sheets("Template").Visible = False

End Sub
Report
Posts
3
Registration date
Tuesday October 4, 2016
Status
Member
Last seen
October 4, 2016
Hi Trowa

Apologies, I now understand what I had done, the room name list I used was a longer one and yes there was a duplicate.

I guess I need to now work on a way of it looking at the Room Name and the Level, so it won't see it as a duplicate.

Thanks so much again for your help.

Kind regards
Rachael
Answer 2 / 2
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
 458
Hi Rachael,

To check if a sheet already exist you can use this custom code: 
Function SheetExists(SheetName As String) As Boolean
SheetExists = False
On Error GoTo NoSuchSheet

If Len(Sheets(SheetName).Name) > 0 Then
SheetExists = True
Exit Function
End If

NoSuchSheet:
End Function


Place this code after the End Sub line and then use this line in your code: 
If Not SheetExists(“MySheetName”) Then


Best regards,
Trowa
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!