About You - Tools - Profile - Gallery

http://www.heelpbook.net/feed   Valid RSS (HeelpBook) - visit http://www.heelpbook.com/feed   http://www.heelpbook.com/feed

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.



Facebook Twitter Email Plusone Linkedin Stumbleupon Delicious Digg
© 2011 - 2013 HeelpBook - Today is: 18/1/2017 Last updated: September 22, 2011 at 10:57 am Get helped -
+ +