VBA to reference newest sheet [Solved]

Report
-
Posts
2644
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 31, 2020
-
Hello,

Im a complete beginner in excel and the vba process. I'm trying to make a macro create a new sheet, copy the style/structure from a pre-determined sheet and paste it there.
I'm just wondering how to edit the code so that it doesn't paste in "sheet3" which is the sheet the code was created from to whatever newest sheet is added.
Thanks

1 reply

Posts
2644
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 31, 2020
432
Hi Pthfnd,

A sheet with a specific structure used to create new sheets with, is called a template sheet.
A template sheet is usually the last sheet. The code below will ask you for a name to give the new sheet. It will then copy the template, give it's name and place it before the template sheet.

Here is the code:
Sub RunMe()
Dim sName As String

sName = InputBox("Enter the name for the new sheet:", "New sheet name")

Sheets("Template").Copy before:=Sheets("Template")
ActiveSheet.Name = sName
End Sub


Let us know how this works out for you.

Best regards,
Trowa

1
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 2855 users have said thank you to us this month