VBA to reference newest sheet [Solved/Closed]

Report
-
Posts
2702
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
March 2, 2021
-
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
2702
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
March 2, 2021
457
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

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!