Previous Topic Next Topic

VBA to reference newest sheet

Solved/Closed
pthfnd - Mar 4, 2020 at 01:05 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Mar 5, 2020 at 11:56 AM
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 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Updated on Jul 23, 2020 at 11:33 AM
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

2