Ask a question Report Reply to this topic

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

RachJol 3 Posts Tuesday October 4, 2016Registration date October 4, 2016 Last seen - Oct 4, 2016 at 11:10 AM - Latest reply: TrowaD 2340 Posts Sunday September 12, 2010Registration dateModeratorStatus April 19, 2018 Last seen

- Oct 6, 2016 at 11:07 AM

Excel Ask a question Report Reply to this topic

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

6 replies

Report Respond to TrowaD

TrowaD 2340 Posts Sunday September 12, 2010Registration dateModeratorStatus April 19, 2018 Last seen - Oct 4, 2016 at 11:21 AM

Helpful

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 3 Posts Tuesday October 4, 2016Registration date October 4, 2016 Last seen - Oct 4, 2016 at 11:53 AM

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 2340 Posts Sunday September 12, 2010Registration dateModeratorStatus April 19, 2018 Last seen > RachJol 3 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.

What did you change?

Best regards,
Trowa

Report

TrowaD 2340 Posts Sunday September 12, 2010Registration dateModeratorStatus April 19, 2018 Last seen - Oct 4, 2016 at 12:08 PM

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 3 Posts Tuesday October 4, 2016Registration date October 4, 2016 Last seen - Oct 4, 2016 at 12:10 PM

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

TrowaD 2340 Posts Sunday September 12, 2010Registration dateModeratorStatus April 19, 2018 Last seen - Oct 6, 2016 at 11:07 AM

Helpful

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