Report

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

Ask a question RachJol 3Posts Tuesday October 4, 2016Registration date October 4, 2016 Last seen - Last answered on Oct 6, 2016 11:07AM
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 
Helpful
+0
moins plus
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
RachJol 3Posts Tuesday October 4, 2016Registration date October 4, 2016 Last seen - Oct 4, 2016 11:53AM
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
Reply
TrowaD 2033Posts Sunday September 12, 2010Registration date ModeratorStatus December 6, 2016 Last seen - Oct 4, 2016 12:01PM
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
Reply
TrowaD 2033Posts Sunday September 12, 2010Registration date ModeratorStatus December 6, 2016 Last seen - Oct 4, 2016 12:08PM
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
Reply
RachJol 3Posts Tuesday October 4, 2016Registration date October 4, 2016 Last seen - Oct 4, 2016 12:10PM
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
Reply
Add comment
Helpful
+0
moins plus
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
Add comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!