CODEWITHSUNDEEP
vbaexcel
Ferfa
Ferfa

Reputation: 221

How to generate sheet from template

In VBA, i would like create a new sheet from a template after my first sheet.

Example: In MyFirstSheet i have cell B16="House" and a button "NewSheetFromTemplate". When user click on the button a new sheet generated after my MyFirstSheet and contains same information than TEMPLATE sheet with title House.

My VBA code:

Sub NewSheetFromTemplate()
Dim sht As Worksheet
   Set sht = Sheets("TEMPLATE").Copy After:=sheets("MyFirstSheet")
   sht.Name = Range("B16").Value
End Sub

Excel say me "Syntax error" but why ?

Upvotes: 2

Views: 2033

Answers (3)

Ferran
Ferran

Reputation: 199

You can't create an instance of a Worksheet via Copy method. Since you know where are you placing the new sheet, you are able to find it after copied, and rename it. You are very close:

Sheets("TEMPLATE").Copy After:=Sheets("MyFirstSheet")
Sheets(Sheets("MyFirstSheet").Index + 1).Name = Sheets("TEMPLATE").Range("B16").Value

Make sure that you have a value in B16 range.

Hope this helps,

Cheers.

Upvotes: 0

Jordan
Jordan

Reputation: 4514

The following line of code does not return an object, one is created but this is not returned to VBA: 

Sheets("TEMPLATE").Copy After:=sheets("MyFirstSheet")

This means that you cannot set this line of code to an object. Instead, try something like either of the following two options:

Using Index

Because you've copied the new worksheet after Sheets("MyFirstSheet") you can use the worksheet index of Sheets("MyFirstSheet") and then add 1 to get the sheet you've just created.

Sub NewSheetFromTemplate()
Dim sht As Worksheet
    Sheets("TEMPLATE").Copy After:=sheets("MyFirstSheet")
    Set sht = Sheets(Sheets("MyFirstSheet").Index+1)
    sht.Name = Range("B16").Value
End Sub

Using "Name (2)"

Alternatively, the default name for a copied sheet is the original name with " (2)" tagged onto the end. This is still a useful way of identifying the new worksheet however it could become an issue if the original worksheet has a particularly long name.

Sub NewSheetFromTemplate()
Dim sht As Worksheet
    Sheets("TEMPLATE").Copy After:=sheets("MyFirstSheet")
    Set sht = Sheets("TEMPLATE (2)")
    sht.Name = Range("B16").Value
End Sub

Upvotes: 1

Limak
Limak

Reputation: 1521

I don't think is it really necessary to create Worksheet object just to rename it. Try simply like this:

Sub NewSheetFromTemplate()
   Sheets("TEMPLATE").Copy After:=Sheets("MyFirstSheet")
   ActiveSheet.Name = Sheets("MyFirstSheet").Range("B16").Value
End Sub

Upvotes: 2

Related Questions