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:
Sheets.Add
ActiveSheet.Name = "Test"
Sheets("Test").Move After:=Sheets(3)
I have tried with following but ...
Sheets.Add
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
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 comarped to ActNm (usually Sheetx)
if = then ask again if sheetname not valid ask
again.
Ivan
Posted by Joakim B on May 25, 2000 10:46 AM
Thanx Ivan!
