Create & name worksheets from a list and copy data [Solved/Closed]
RachJol3
Posts Tuesday October 4, 2016Registration date
October 4, 2016
Last seen
-
Oct 4, 2016 at 11:10 AM - Latest reply: TrowaD2435
Posts Sunday September 12, 2010Registration dateModeratorStatus
October 8, 2018
Last seen
- Oct 6, 2016 at 11:07 AM
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
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.
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.
TrowaD2435
Posts Sunday September 12, 2010Registration dateModeratorStatus
October 8, 2018
Last seen
>
RachJol3
Posts Tuesday October 4, 2016Registration date
October 4, 2016
Last seen -Oct 4, 2016 at 12:01 PM
Hi Rachael,
That would suggest a duplicate entry in your Room name list.
I downloaded your file, run the code and no error occured.
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
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:
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
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
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 SubApologies, 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