checking for duplicate worksheets

  • Thread starter Thread starter Rick B
  • Start date Start date
R

Rick B

I have written a little VBA to create a new worksheet tab. The naming of
this tab is obtained from a specific cell. If a worksheet name already
matches data that is entered into that cell, an error occurs. How do I first
check if the worksheet name does not already exist before trying to rename
the new sheet?

Sheets("Template").Select
Sheets("Template").Copy After:=Sheets(2)
Sheets("Template (2)").Select
ActiveSheet.Name = Sheets("StartHere").Range("B3")

Thanks in advance,

Rick
 
Hi Rick

You can trap the error like this

On Error Resume Next
ActiveSheet.Name = Sheets("StartHere").Range("B3")
On Error GoTo 0

Or use a function to check if the sheet exist

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

You can use this line then

If SheetExists("test") = True Then
 
Back
Top