worksheets collection... find...

  • Thread starter Thread starter mark kubicki
  • Start date Start date
M

mark kubicki

what function do i use to determine if a worksheet name already exists;

or should i use error trapping to catch an attempt at creating a redundant
worksheet name?
 
You can error trap or use a function

Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If WB Is Nothing Then Set WB = ThisWorkbook
SheetExists = CBool(Len(WB.Sheets(SName).Name))
End Function


This in you macro

If SheetExists("Master") = True Then....


Or

Sub sheettest()
Dim N As Long
On Error Resume Next
With ThisWorkbook.Worksheets
N = Len(.Item("Report").Name)
If N = 0 Then
'your code
End If
End With
End Sub
 
proposedname = "Sheet1"
for each sh in worksheets
if lcase(sh.name) = lcase(proposedname) then
msgbox "Name is a duplicate"
exit for
end if
Next
End Sub

or you can error trap.
 
Back
Top