Adding a new sheet in code

[AccessShell]

I ran "Record New Macro" to get code to add, move and rename a new sheet. The macro made a new sheet and automatically named it "Sheet1" I already had a sheet named "522 Details".

Then I deleted the sheet added by the code, and ran the code. Instead of naming the new sheet "Sheet1" it named it "Sheet2" This does not work well for me as I will need to run this macro several times. Can I adjust the macro code so the new sheet always has a default name before I can change the name for my needs?

Sub AddASheet()
'
' AddASheet Macro
'
Dim strNewSheetName As String
'
    strNewSheetName = Format(Now, "mmyy") & " Details"
   
    Sheets("522 Details").Select
    Sheets.Add
    Sheets("Sheet1").Select
    Sheets("Sheet1").Move After:=Sheets(2)
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = strNewSheetName
   
End Sub

Thanks

 

[Fluff]

The name of the new sheet is irrelevant, you can use

Sub AddASheet()
'
' AddASheet Macro
'
Dim strNewSheetName As String
'
    strNewSheetName = Format(Now, "mmyy") & " Details"
   
    Sheets.Add(, Sheets(2)).Name = strNewSheetName
   
End Sub

 

[AccessShell]

Thanks Fluff. exactly what I needed.

 

[Fluff]

You're welcome & thanks for the feedback.