when adding worksheet, how do I make sure it is always the first worksheet?

When adding a worksheet with VBA, how to I make sure the new worksheet is always the first worksheet in the workbook?
Sandra SmithRetiredAsked:
Who is Participating?
Patrick MatthewsConnect With a Mentor Commented:
Use a statement like this:

With ActiveWorkbook
    .Worksheets.Add Before:=.Worksheets(1)
End With

Open in new window

To make it the first sheet:

With ActiveWorkbook
    .Worksheets.Add Before:=.Sheets(1)
End With

Open in new window

Sandra SmithRetiredAuthor Commented:
Thank you,

Here is a subroutine that will make it the first sheet and ask to be renamed

Sub addsheet()
' addsheet Macro
' Keyboard Shortcut: Ctrl+a

Dim ActNm As String

With ActiveWorkbook.Sheets
    .Add Before:=Worksheets(1)
End With
ActNm = ActiveSheet.Name
On Error Resume Next
ActiveSheet.Name = "Sheet1"
NoName: If Err.Number = 1004 Then ActiveSheet.Name = InputBox("Give name.")
If ActiveSheet.Name = ActNm Then GoTo NoName
On Error GoTo 0
End Sub
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.