CODEWITHSUNDEEP
vbaexcel
luke
luke

Reputation: 482

VBA Store current sheet name to use on new sheet

I am wanting to have excel remember the active sheet name and then delete the active sheet and copy a different sheet that i use as a template and rename the new sheet with the stored name from the deleted sheet.

This is the code im using i just need to have it rename the sheet with the stored sheet name

Sub Reset_Work_Order()

Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete


Dim NewSht              As Worksheet

Master_Work_Order.Copy After:=ActiveSheet
Set NewSht = ActiveSheet

End Sub

Upvotes: 0

Views: 4470

Answers (1)

Shai Rado
Shai Rado

Reputation: 33692

Try the code below:

Option Explicit

Sub Reset_Work_Order()

Dim ShtName As String
Dim Master_Work_Order As Worksheet
Application.DisplayAlerts = False

Set Master_Work_Order = Worksheets("YourMasterSheetsName")

' keep name of ActiveSheet
ShtName = ActiveSheet.Name
ActiveSheet.Delete

Dim NewSht              As Worksheet

Master_Work_Order.Copy After:=ActiveSheet

Set NewSht = ActiveSheet
' set new copied sheet name to Previous ActiveSheet's name
NewSht.Name = ShtName

End Sub

Upvotes: 2

Related Questions