VBA to Check if Worksheet Exists in A Workbook

[juan4412]

I have an array that opens a workbook containing close to 100 worksheets, and copies specific sheet names into there own individual workbooks. The problem I have now, is that I assume the worksheet exists in the workbook, but often times it does not Is there a way to add some sort of "catch" that will 1st verify the worksheet exists instead of my code crashing?

 

[VoG]

Try like this

Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
End Function

Sub test()
If Not WorksheetExists("Sheet1") Then
        MsgBox "Unable to proceed", vbExclamation, "Error"
        Exit Sub
End If
End Sub

 

[juan4412]

How would I incorporate that into my current code:

Dim varBooks
Dim varbook
Dim ws As Excel.Worksheet
Dim wb As Excel.Workbook
 
varSheets = Array("Rain", "Sunshine", "Snow")
 
For Each varSheet In varSheets
    Set wb = Workbooks.Open(Filename:="R:\Weather.xls", ReadOnly:=True)
    With wb.Sheets(varSheet)
        .Copy
        ActiveWorkbook.SaveAs Filename:="O:\WeatherReport" & .Name & VBA.Format(Date, "mmddyyyy") & ".xls"
    End With
Next varSheet

And also if the sheet does not exist, is there a way to "do nothing" and just continue processing like normal?

 

[VoG]

Try

For Each varsheet In varSheets
    If worksheetexists(varsheet) Then
        Set wb = Workbooks.Open(Filename:="R:\Weather.xls", ReadOnly:=True)
        With wb.Sheets(varsheet)
            .Copy
            ActiveWorkbook.SaveAs Filename:="O:\WeatherReport" & .Name & VBA.Format(Date, "mmddyyyy") & ".xls"
        End With
    End If
Next varsheet

 

[Jonmo1]

Here is an alternate method using Select Case...
Without a need to test if the sheet exists.

Dim ws As WorkSheet
For Each ws In Worksheets
    Select Case ws.Name
        Case "Rain", "Sunshine", "Snow"
            'Code here to run on sheets listed above
        Case Else
            'Do Nothing
    End Select
Next ws

 

[juan4412]

VOG trying to use your code, and on the line that says -----
If worksheetexists(varsheet) Then

It throws a compile error of Sub or function is not defined?

 

[VoG]

You need to add this (which I posted in my first reply)

Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
End Function

 

[juan4412]

Oh My apologies! Is there a way to put the Function in a module of it's own and set that entire module to "Public" so that every module I want to use the code in can see the Function?

Or does this Function have to be copied into each module that the code is going to be executed in?

 

[VoG]

If you use the following it can be accessed from any module

Public Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
End Function

 

[juan4412]

I receive a compile error of this
ByRef argument type mismatch
highlighted code is
If WorksheetExists(varsheet) Then