checking to see if worksheet exists

[fluxcapacitr609]

bCheck = Len(Sheets("Jan_2018").Name) 
If bCheck = True Then
    Set myCell = Sheets("Jan_2018")

Trying to see if a worksheet exists and if it does do something and if not skip, but it doesnt skip it always keeps writing on the previous sheet.

 

[mumps]

Maybe:

Sub TestForWorksheet()
    	Dim wsSheet As Worksheet
	On Error Resume Next
	Set wsSheet = Sheets("Jan_2018")
	On Error GoTo 0
	If Not wsSheet Is Nothing Then
    		MsgBox "I do exist"
	Else
    		MsgBox "I do NOT exist"
	End If	
End Sub

 

[wideboydixon]

At worst, bCheck will be 8 which is not the same as True. This function will check if a sheet exists:

Public Function SheetExists(sheetName As String) As Boolean

Dim testSheet As Worksheet

On Error Resume Next
Err.Clear
Set testSheet = Sheets(sheetName)
SheetExists = Not (testSheet Is Nothing)

End Function

Used like this:

If SheetExists("Jan_2018") Then
    ' Do something
End If

WBD

 

[Fluff]

Another option

Sub chk()
Dim bcheck As Boolean

bcheck = Evaluate("isref(Jan_2018!A1)")
If bcheck Then
   MsgBox True
Else
   MsgBox False
End If
End Sub

 

[mikerickson]

Here is another UDF

Function WorksheetExists(sheetName as String, optional workbookName as String) As Boolean

    If workbookName = vbNullString then workbookName = ActiveWorkbook.Name

    On Error Resume Next
    WorksheetExists = (LCase(Workbooks(workbookName).Sheets(sheetName).Name) = LCase(sheetName))
    On Error Goto 0
End Function

 

[fluxcapacitr609]

Fluff! That worked! So great. Thank you.

 

[Fluff]

Glad we could help & thanks for the feedback