Check whether a worksheet exists already

  • Thread starter Thread starter clui
  • Start date Start date
C

clui

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!
 
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
Click to expand...
creating financial statements
 
Clui,

Try something like this:

Sub RenameToAll()

Dim CurrSht As String
Dim ws As Worksheet
Dim AlreadySet As String
Const SearchFor As String = "All"

CurrSht = ActiveSheet.Name
For Each ws In ThisWorkbook.Worksheets
If UCase(ws.Name) = UCase(SearchFor) Then
AlreadySet = ws.Name
Exit For
End If
Next ws

If Not AlreadySet = "" Then
MsgBox "A sheet named [" & AlreadySet & "] already exists in
the Workbook"
Exit Sub
Else
Sheets(CurrSht).Name = "All"
End If

End Sub

THis will set the current sheet name to "All" if another sheet in the
workbook is not named "All". I have set it up so "All" is not case
sensitive.

Alex J


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
Click to expand...
creating financial statements
 
Back
Top