Menu
Ask a question Report

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

Posts
3
Registration date
Tuesday October 4, 2016
Last seen
October 4, 2016
 - - Latest reply: TrowaD
Posts
2441
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 18, 2018
 - Oct 6, 2016 at 11:07 AM
Excel Ask a question Report
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
See more 

2 replies

Report Respond to TrowaD
Posts
2441
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 18, 2018
0
Thank you
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
RachJol
Posts
3
Registration date
Tuesday October 4, 2016
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
TrowaD
Posts
2441
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 18, 2018
 > RachJol
Posts
3
Registration date
Tuesday October 4, 2016
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
TrowaD
Posts
2441
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 18, 2018
 -
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
RachJol
Posts
3
Registration date
Tuesday October 4, 2016
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
Report Respond to TrowaD
Posts
2441
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 18, 2018
0
Thank you
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