Help with some code please

  • Thread starter Thread starter Gareth
  • Start date Start date
G

Gareth

I have the following code:

With Worksheets("mysheet")
If Not IsEmpty(.Range("A2").Value) Then
'do stuff
End If
End With

My problem is that mysheet is not always a sheet in the file. How can I get
Excel to miss out the code above if mysheet does not exist in the file?

Thanks in advance.

Gareth
 
Hi try this

Dim sh As Worksheet
For Each sh In Worksheets
If sh.Name = "mysheet" Then
With Worksheets("mysheet")
If Not IsEmpty(.Range("A2").Value) Then
'do stuff
End If
End With
End If
Next sh

libby
 
dim testWks as worksheet

set testWks = nothing
on error resume next
set testwks = worksheets("mysheet")
on error goto 0

if testwks is nothing then
'not there
else
if not isempt(....


would be one way. If you do it lots, instead of putting all that inline code in
your macro, you could call a function:

This is one that I've saved from Chip Pearson:


Function WorksheetExists(SheetName As String, _
Optional WhichBook As Workbook) As Boolean
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = Len(WB.Worksheets(SheetName).Name) > 0
End Function

You can then call this function in code as follows:

If WorksheetExists("Sheet123") = True Then
' sheet exists
Else
' sheet does not exist
End If
 
Back
Top