try. Don't remember where I got it.
Sub AddSheetWithNameCheckIfExists()
Dim WS As Worksheet
Dim newSheetName As String
newSheetName = Sheets(1).Range("A1") ' Substitute your range here
For Each WS In Worksheets
If WS.Name = newSheetName Or newSheetName = "" Or
IsNumeric(newSheetName) Then
MsgBox "Sheet already exists or name is invalid", vbInformation
Exit Sub
End If
Next
Sheets.Add Type:="Worksheet"
With ActiveSheet
.Move After:=Worksheets(Worksheets.Count)
.Name = newSheetName
End With
End Sub
Sub mynewsheets()
'Dim c As Range
For Each c In Range("myrange")
On Error Resume Next
If Sheets.Name <> c Then
Sheets.Add.Name = c
End If
Next c
End Sub
--
Don Guillett
SalesAid Software
(e-mail address removed)
clui said:
I need to check whether a worksheet with the name "all" already exists
in my workbook. If so, I don't name another worksheet "all", otherwise
I have to name it "all". Thanks!
------------------------------------------------
~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements