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

Dim varBooks
Dim varbook
Dim varsheet
Dim varsheets(0 To 2) As String
Dim ws As Excel.Worksheet
Dim wb As Excel.Workbook
Dim i
 varsheets(0) = "Rain"
 varsheets(1) = "Sunshine"
 varsheets(2) = "Snow"
Set wb = Workbooks.Open(Filename:="R:\Weather.xls", ReadOnly:=True)
For i = LBound(varsheets) To UBound(varsheets)
    If WorksheetExists(varsheets(i)) Then
        With wb.Sheets(varsheets(i))
            .Copy
            ActiveWorkbook.SaveAs Filename:="O:\WeatherReport" & .Name & VBA.Format(Date, "mmddyyyy") & ".xls"
        End With
    End If
Next i

 

[juan4412]

It now changed to a type mismatch on
With wb.Sheets(varsheet(i))

 

[VoG]

It should be

With wb.Sheets(varsheets(i))

 

[jo15765]

This will open and save the 1st sheet in the array, and then it stops. It does not proceed past that point...

 

[VoG]

It should copy all the matching sheets. Do you get an error message?

 

[juan4412]

I'm with JO -

It compiles fine, no debug error. Just saves the 1st file in the array, and then sits there.

 

[Norie]

The function WorksheetExists might be looking for the worksheet in the wrong workbook.

 

[juan4412]

This is the function:

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

I do not see where it is pointing to a worksheet?
</pre>

 

[Norie]

In the code it points to a worksheet with the name passed to it.

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

However it doesn't point to a workbook, so it will be trying to see if the worksheet exists in the active workbook.

As far as I can see the active workbook when it's called after copying the first worksheet is the new workbook that's been created.

So the WorksheetExists function returns false because the worksheet it's looking for isn't in the active workbook.

Try this.

Option Explicit
Sub test()
Dim varBooks
Dim varbook
Dim varsheet
Dim varsheets(0 To 2) As String
Dim ws As Excel.Worksheet
Dim wb As Excel.Workbook
Dim i
    varsheets(0) = "Rain"
    varsheets(1) = "Sunshine"
    varsheets(2) = "Snow"
    Set wb = ThisWorkbook
    For i = LBound(varsheets) To UBound(varsheets)
        If WorksheetExists(wb.Name, varsheets(i)) Then
            With wb.Sheets(varsheets(i))
                .Copy
                ActiveWorkbook.SaveAs Filename:="C:\WeatherReport" & .Name & VBA.Format(Date, "mmddyyyy") & ".xls"
            End With
        End If
    Next i
End Sub
Function WorksheetExists(WBName As String, WSName As String) As Boolean
    On Error Resume Next
    WorksheetExists = Workbooks(WBName).Worksheets(WSName).Name = WSName
    On Error GoTo 0
End Function

 

[tusharm]

I haven't gone through 3 pages of discussion on what should be a (relatively) simple issue. {grin}

Maybe, someone has already given you an approach similar to the below and it doesn't meet your requirements but it appears that it should.

Try the untested

Option Explicit
Sub copySheets1()

    Dim varSheets: varSheets = Array("Rain", "Sunshine", "Snow")
     
    Dim WB As Excel.Workbook
    Set WB = Workbooks.Open(Filename:="R:\Weather.xls", ReadOnly:=True)
    
    Dim varSheet
    For Each varSheet In varSheets
        On Error Resume Next
        Dim WS As Object
        Set WS = Nothing: Set WS = WB.Sheets(varSheet)
        On Error GoTo 0
        If Not WS Is Nothing Then
            With WS
            .Copy
            ActiveWorkbook.SaveAs _
                Filename:="O:\WeatherReport" & .Name _
                    & VBA.Format(Date, "mmddyyyy") & ".xls"
            Application.DisplayAlerts = False
            ActiveWorkbook.Close False
            Application.DisplayAlerts = True
                End With
            End If
        Next varSheet
    End Sub

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?