Create & name worksheets from a list and copy dataSolved/Closed

Question

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

TrowaD · Answer

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.

TrowaD · Answer

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

Create & name worksheets from a list and copy data

2 responses

Similar discussions

Subscribe To Our Newsletter!