Excel – Create and rename new sheets using VBA.

The following code can be used as a macro in an Excel workbook to create a new worksheet:

Sub AddSheet()


Dim ActNm As String


With ActiveWorkbook.Sheets


.Add after:=Worksheets(Worksheets.Count)


End With


ActNm = ActiveSheet.Name


On Error Resume Next


ActiveSheet.Name = "Test"


NoName: If Err.Number = 1004 _


Then ActiveSheet.Name = InputBox("Give name.")


If ActiveSheet.Name = ActNm Then GoTo NoName


On Error GoTo 0


End Sub

What it does

• Adds sheet after the last sheet which defaults to Sheet(Index number) (Example: Sheet4)
• This is stored in variable ActNm so as to have a comparison.
• If sheet exists (eg 1st time running Test doesn't exist, after that it does, so you get an error 1004.) then Error 1004 generated and you are asked to input New name.

New name is compared to ActNm (usually Sheetx) so, if it already exists then it will ask again or if the sheet's name is not valid it'll ask again.