Create a new sheet from a template sheet

Question

i have created a vba script which helps me to create new sheets in my workbook every time i enter a new column. What i want to change is to create a new sheet but copying a template sheet for that new sheet.

I basically create a "home sheet" where i will divide the curriculum into lessons, then i want the script to run and create a lesson plan sheet for each lesson. Please can someone help me with this?

Sub add()

Call CreateWorksheets(Sheets("Lesson List").Range("B2:XFD2"))

End Sub

Sub CreateWorksheets(Names_Of_Sheets As Range)
Dim No_Of_Sheets_to_be_Added As Integer
Dim Sheet_Name As String
Dim i As Integer

No_Of_Sheets_to_be_Added = Names_Of_Sheets.Columns.Count

For i = 1 To No_Of_Sheets_to_be_Added

Sheet_Name = Names_Of_Sheets.Cells(1, i).Value

If (Sheet_Exists(Sheet_Name) = False) And (Sheet_Name <> "") Then
    Worksheets.add(After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)).Name = Sheet_Name
End If

Next i

End Sub

Function Sheet_Exists(WorkSheet_Name As String) As Boolean
Dim Work_sheet As Worksheet

Sheet_Exists = False

For Each Work_sheet In ThisWorkbook.Worksheets

    If Work_sheet.Name = WorkSheet_Name Then
        Sheet_Exists = True
    End If

Next

End Function

My edited code with me trying to use the copy function:

   Sub add()

Call CreateWorksheets(Sheets("Lesson List").Range("B2:XFD2"))

End Sub

Sub CreateWorksheets(Names_Of_Sheets As Range)
Dim No_Of_Sheets_to_be_Added As Integer
Dim Sheet_Name As String
Dim i As Integer
'determine the number of sheets to create
No_Of_Sheets_to_be_Added = Names_Of_Sheets.Columns.Count

For i = 1 To No_Of_Sheets_to_be_Added
'lable each sheet
Sheet_Name = Names_Of_Sheets.Cells(1, i).Value

If (Sheet_Exists(Sheet_Name) = False) And (Sheet_Name <> "") Then
    Worksheets("Lesson Plan Template").Copy After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count).Name = Sheet_Name
       
    End If

Next i

End Sub

Function Sheet_Exists(WorkSheet_Name As String) As Boolean
Dim Work_sheet As Worksheet

Sheet_Exists = False

For Each Work_sheet In ThisWorkbook.Worksheets

    If Work_sheet.Name = WorkSheet_Name Then
        Sheet_Exists = True
    End If

Next

End Function

You might benefit from reading [Why is “Can someone help me?” not an actual question?](https://meta.stackoverflow.com/a/284237/3219613) • You should ask a question related to your code. Tell us what you tried and what is wrong with your code. Any errors? Where exactly did you get stuck? Note that a list of requirements or what you want to do is not a question according to [ask]. — Pᴇʜ, Sep 14 '20 at 07:21
I am not sure why this question has been associated with another question. Mine is about creating new sheets based on a column cell completed and the new sheet being created should be a copy from a different sheet. If you look at the other question they are definitely very different questions? — Ben Rabe, Sep 14 '20 at 07:33
Please check my comment above. You didn't ask a (real) question yet and you did not explain what is wrong with your code and why you could not achieve what you want. Actually creating a new sheet out of a template is the same as copying the template (which the other question shows how to do that). If that doesn't anwser your question, maybe you need to ask more precisely. Note you can [edit] your question to improve it. — Pᴇʜ, Sep 14 '20 at 07:36
I created the script which is creating a new sheet each time I complete a cell in the range (column in row two). This works but it creates a new blank sheet (because i used the create/ add function i assume) I want to create a new sheet but the new sheet should copy the contents of a template sheet. The main sheet is "lesson list" and the template sheet is "lesson plan template" When i try to change the add to copy it keeps rejecting my changes to the code. I am not very familiar with VBA so i cannot explain what is wrong i can just say that the script errors out on me. — Ben Rabe, Sep 14 '20 at 07:37
i think that this is the line where the problem is: `If (Sheet_Exists(Sheet_Name) = False) And (Sheet_Name <> "") Then Worksheets.add(After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)).Name = Sheet_Name` — Ben Rabe, Sep 14 '20 at 07:41
*"it keeps rejecting my changes to the code"* what does that mean exactly? And in your code you use `Worksheets.Add` which will add a new blank worksheet. Please check the link above your question which shows how to **copy** a sheet instead of adding a blank one. — Pᴇʜ, Sep 14 '20 at 07:44
When i try to paste the screenshot of the error it tells me that we cannot do it? — Ben Rabe, Sep 14 '20 at 07:45
It means that it says "Compile error" - expected function or variable — Ben Rabe, Sep 14 '20 at 07:46
Ah, well the screenshot shows different code than your question. Of course we cannot know that if you don't [edit] your code. The issue is you did not tell VBA **which** sheet to copy! Check the link again (precisely) or check the documentation of the [Worksheet.Copy method](https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.copy). This will solve your issue. — Pᴇʜ, Sep 14 '20 at 07:47
the link of copying a sheet simply copies a sheet, the moment i try to write it in a way which it will not just copy a sheet but that the macro will check if any column cell has been filled - i again get the "compile error" so the problem i am encountering is that using the copy function in the code i wrote keeps returning with an compile error — Ben Rabe, Sep 14 '20 at 07:48
Okay, i will try and send you the new code so that you can see the code i try to use to copy — Ben Rabe, Sep 14 '20 at 07:50
The compile error is because you did not do **exactly** as the link and the documentation says. You used `Worksheets.Copy(…)` but all the links I gave you say `Worksheets("template").Copy(…)`. As I said you need to make clear **which** worksheet to copy. How can VBA know which sheet is your template to copy if you don't tell VBA? There is no magic, everything has do be defined correcty. Take your time to read the documentation and don't hurry, then you will get it working. — Pᴇʜ, Sep 14 '20 at 07:52
`Sub CreateWorksheets(Names_Of_Sheets As Range) Dim No_Of_Sheets_to_be_Added As Integer Dim Sheet_Name As String Dim i As Integer 'determine the number of sheets to create No_Of_Sheets_to_be_Added = Names_Of_Sheets.Columns.Count For i = 1 To No_Of_Sheets_to_be_Added 'lable each sheet Sheet_Name = Names_Of_Sheets.Cells(1, i).Value If (Sheet_Exists(Sheet_Name) = False) And (Sheet_Name <> "") Then Worksheets("lesson plan template").Copy After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)).Name = Sheet_Name End If Next i` — Ben Rabe, Sep 14 '20 at 07:54
Please don't post code in comments. [Edit] your question instead. It is unreadable. — Pᴇʜ, Sep 14 '20 at 07:55
The error i get is "run-time error 1004: copy method of worksheet class failed — Ben Rabe, Sep 14 '20 at 07:57
because the parameter `After` is waiting for a sheet object but you gave it a `String` (with the `.Name`). So you need to add parenthesis: `Worksheets("Lesson Plan Template").Copy( After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)).Name = Sheet_Name` or you need to do it in multiple lines, as shown in the link above your question. — Pᴇʜ, Sep 14 '20 at 09:08
Thank you very much for your help! I previously had it in the parenthesis and it results in a run-time error 424 - object required - i have been battling with this for a few days by now hence asking for help here. I will try the multiple lines. — Ben Rabe, Sep 14 '20 at 09:29

Pᴇʜ · Answer 1 · 2020-09-14T16:01:31.317

Check out the following solution including some proper error handling.

Sub CreateWorksheets(Names_Of_Sheets As Range)
    'determine the number of sheets to create
    Dim No_Of_Sheets_to_be_Added As Long
    With Names_Of_Sheets
        No_Of_Sheets_to_be_Added = .Resize(ColumnSize:=1).Offset(ColumnOffset:=.Columns.Count - .Column + 1).End(xlToLeft).Column - .Column + 1
    End With

    Dim i As Long
    For i = 1 To No_Of_Sheets_to_be_Added

        Dim Sheet_Name As String
        Sheet_Name = Names_Of_Sheets.Cells(1, i).Value
 
        If Not Sheet_Exists(Sheet_Name) And Sheet_Name <> vbNullString Then
            Dim TemplateCopy As Worksheet
            Set TemplateCopy = Nothing 'initialize (needed because we are within a loop)

            On Error Goto COPY_TEMPLATE_ERROR 'if error occurs throw message
            Set TemplateCopy = Worksheets("Lesson Plan Template").Copy(After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count))
            On Error Goto 0 're-enable error handling!

            If Not TemplateCopy Is Nothing Then 'check if the template copy exists
                On Error Goto RENAME_COPY_ERROR
                TemplateCopy.Name = Sheet_Name
                On Error Goto 0 're-enable error handling!
            End If
        End If

    Next i


    Exit Sub
COPY_TEMPLATE_ERROR:
    MsgBox "Template worksheet could not be copied."
    Resume

RENAME_COPY_ERROR:
    MsgBox "Template could not be renamed to '" & Sheet_Name & "'!"

    'remove the template copy that could not be renamed (or you will have orphaned template copys)
    Application.DisplayAlerts = False
    TemplateCopy.Delete
    Application.DisplayAlerts = True
    Resume
End Sub

Make sure ActiveWorkbook is what you actually mean it to be:

ActiveWorkbook is the workbook that has focus (is on top of the other windows) while the code runs. This can easily change by a single mouse click.
ThisWorkbook is the workbook this VBA code is written in. It will never change. Use this over ActiveWorkbook when ever possible. This is much more reliable.

Note that if you work with multiple workbooks, every Worksheets or Sheets object needs to start with either ActiveWorkbook.Worksheets, ThisWorkbook.Worksheets or Workbooks("your-workbook-name.xlsm").Worksheets otherwise it is not clear for VBA which one of these you actually mean and it makes a guess (and it might guess wrong).

Finally the name of your function Sheet_Exists is a bit missleading because it only works for Worksheets as it is coded right now. Make sure you know the difference:

Sheets() contains all type of sheets: Worksheet, chart sheet, etc.
Worksheets() only contains sheets of type worksheet.

So your function should be called Worksheet_Exists

Function Worksheet_Exists(WorkSheet_Name As String) As Boolean
    Dim Work_sheet As Worksheet
    For Each Work_sheet In ThisWorkbook.Worksheets
        If Work_sheet.Name = WorkSheet_Name Then
            Worksheet_Exists = True
            Exit Function 'if we found the name we don't need to check further worksheets
        End If
    Next Work_sheet 
End Function

Or it needs to be changed to work for all type of sheets:

Function Sheet_Exists(Sheet_Name As String) As Boolean
    Dim Sheet As Object
    For Each Sheet In ThisWorkbook.Sheets
        If Sheet.Name = Sheet_Name Then
            Sheet_Exists = True
            Exit Function 'if we found the name we don't need to check further sheets
        End If
    Next Sheet 
End Function

Thank you. Somehow I manage to get an endless loop now - adding sheets continuously - where i have to forcefully close excel with the end task function. — Ben Rabe, Sep 14 '20 at 14:05
Check the value of `No_Of_Sheets_to_be_Added` it goes through all columns in `B2:XFD2` no matter if there is data in it or not. Maybe you should reduce it to the columns that have data. See my edited answer above. — Pᴇʜ, Sep 14 '20 at 16:02

score 0 · Answer 2 · answered Sep 14 '20 at 15:41

Although i have not tidy up the error handling and my function as suggested i did follow the suggest of adding additional steps which gave me the desirable results:

Sub Add_New_Lesson()

Call Copy_Lesson_Template(Sheets("Lesson List").Range("B2:XFD2"))


End Sub


Sub Copy_Lesson_Template(Names_Of_Sheets As Range)

Dim No_Of_Sheets_to_be_Added As Integer
Dim Sheet_Name As String
Dim i As Integer

No_Of_Sheets_to_be_Added = Names_Of_Sheets.Columns.Count

For i = 1 To No_Of_Sheets_to_be_Added
Sheet_Name = Names_Of_Sheets.Cells(1, i).Value


If (Sheet_Exists(Sheet_Name) = False) And (Sheet_Name <> "") Then
Worksheets("Lesson Template").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = (Sheet_Name)

End If

Next i

End Sub


Function Sheet_Exists(WorkSheet_Name As String) As Boolean
Dim Work_sheet As Worksheet

Sheet_Exists = False

For Each Work_sheet In ThisWorkbook.Worksheets

    If Work_sheet.Name = WorkSheet_Name Then
        Sheet_Exists = True
    End If

Next

End Function

You should avoid using `ActiveSheet` where ever you can. It is a bad practice to use because it is not reliable. In this case you can easily avoid it using the same technique as here: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Sep 15 '20 at 05:55

Create a new sheet from a template sheet

2 Answers2