Archive of Mr Excel Message Board

Back to Excel VBA archive index
Back to archive home

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

Check out our Excel VBA Resources

Re: Create and rename new sheets using VBA.

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


Re: Create and rename new sheets using VBA.

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

This archive is from the original message board at
All contents © 1998-2004
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.