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.

SOURCE

LINK

LANGUAGE
ENGLISH
Facebook Twitter Email Plusone Linkedin Stumbleupon Delicious Digg
Add Comment Register



   
© 2011 - 2013 HeelpBook - Today is: 21/12/2014 Last updated: October 7, 2011 at 9:38 am Get helped -