The following code can be used as a macro in an Excel workbook to create a new worksheet:
Dim ActNm As String
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
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.