Excel VBA Copy sheet. Rename it and add it

July 26, 2023

I have a loop that creates sheets based upon a column.
Now I want to change the loop so that the copied sheet is a ‘template’

This is the current "code" that creates the sheets.

Set wksNew = ActiveWorkbook.Worksheets.Add(After:=Sheets(Sheets.Count))
wksNew.Name = rngCell.Value

All variables are defined.

The change would be this:

Set wksNew = ActiveWorkbook.Worksheets("Base").Copy(After:=Sheets(Sheets.Count))
wksNew.Name = rngCell.Value

So I created a "template" with the name "Base"
It only works once then it spawns an error:
Object Required

>Solution :

Copy is a method of WorkSheet object. It doesn’t have return value.

Please ref to Micorsoft document.

https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.copy?WT.mc_id=M365-MVP-33461

Please try it.

Sub demo()
    Dim rngCell As Range
    ' Dim wksNew As Worksheet
    ' Assuming new sheets name is in Sheet1.Range("A1:A3")
    For Each rngCell In Sheet1.Range("A1:A3")
        ActiveWorkbook.Worksheets("Base").Copy after:=Sheets(Sheets.Count)
        ActiveSheet.Name = rngCell.Value
    Next
End Sub