MrExcel Consulting
Your One Stop for Excel Tips & Solutions

Create and rename new sheets using VBA.

Posted by Joakim B on May 18, 2000 6:37 AM

I have a "small" problems with VBA in Excel. I want to that sheet "Test" shall be created if it doesent exists otherwise, you hav to fill in a new name in a box. And the new namnet mustn't exist among the sheets in workbook. If it exists then shall the box run again.

Shold be very grateful for hints / solution on this small problems.

A part of the code:


ActiveSheet.Name = "Test"

Sheets("Test").Move After:=Sheets(3)

I have tried with following but ...


If Error Then GoTo bugs

bugs: ActiveSheet.Name = InputBox("Give name.")

GoTo back

ActiveSheet.Name = "Test"

back: ActiveSheet.Move After:=Sheets(3)

Best regards Joakim Björnberg

Posted by Ivan Moala on May 18, 2000 3:40 PM

Hi Joakim
Try this;

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) eg Sheet4
This is stored in Variable ActNm so as to have a
- 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 comarped to ActNm (usually Sheetx)
if = then ask again if sheetname not valid ask


Posted by Joakim B on May 25, 2000 10:46 AM

Thanx Ivan!