test if a sheet exist (with the name) ?

  • Thread starter Thread starter LoloSoft
  • Start date Start date
L

LoloSoft

Hi,

i'm looking for a way to know if a sheet exist with the name (ex : "01J")
if it doesn't exist , i have to create it ...

thanks for any help.
 
Laurent,

Here is some code

Sub test()

If Not SheetExists("ABC") Then
ActiveWorkbook.Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "ABC"
End If

End Sub
Function SheetExists(Filename As String)
Dim oSh As Worksheet
On Error Resume Next
Set oSh = ActiveWorkbook.Worksheets(Filename)
On Error GoTo 0
SheetExists = Not oSh Is Nothing
End Function



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Laurent,

Something like this will work:

Public Function gbWSExists(rsShtName As String) As Boolean
On Error Resume Next
gbWSExists = Len(Worksheets(rsShtName).Name)
On Error GoTo 0
End Function

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
I am making the assumption 01J would be a worksheet.

Dim sh as Worksheet
On error resume next
set sh = Worksheets("01J")
On Error goto 0
if sh is nothing then
worksheets.Add(After:=Worksheets(worksheets.count)).Name ="01J"
set sh = worksheets("01J")
End if
 
Back
Top