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]

Sorry, try like this

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"
 
For i = LBound(varsheets) To UBound(varsheets)
    MsgBox WorksheetExists(varsheets(i))
Next i

 

[juan4412]

I added your code and then added in the copy code that I had previously..I may have added it in the wrong place, since I am getting a debug error or this message Obect Variable or With Block Variable Not Set

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"
 
For i = LBound(varsheets) To UBound(varsheets)
        With wb.Sheets(varsheet)
            .Copy
            ActiveWorkbook.SaveAs Filename:="O:\WeatherReport" & .Name & VBA.Format(Date, "mmddyyyy") & ".xls"
        End With
Next i

 

[VoG]

You haven't assigned a value to wb.

 

[juan4412]

I thought at the top of the code where I set

Dim wb As Excel.Workbook

That gave wb a value?

Anywho...I tried to change it to ActiveWorkbook.Name, and Workbook.Name and both of those throw an error. What value would I need to set this as so that it copies the specific sheet in the array it is on?

 

[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 = ActiveWorkbook
For i = LBound(varsheets) To UBound(varsheets)
        With wb.Sheets(varsheet)
            .Copy
            ActiveWorkbook.SaveAs Filename:="O:\WeatherReport" & .Name & VBA.Format(Date, "mmddyyyy") & ".xls"
        End With
Next i

 

[juan4412]

That throws a subscript out of range message on the line of with wb.sheets

 

[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 = ActiveWorkbook
For i = LBound(varsheets) To UBound(varsheets)
    If WorksheetExists(varsheets(i)) Then
        With wb.Sheets(varsheet)
            .Copy
            ActiveWorkbook.SaveAs Filename:="O:\WeatherReport" & .Name & VBA.Format(Date, "mmddyyyy") & ".xls"
        End With
    End If
Next i

 

[juan4412]

That debugs okay, but I just realized It no longer has my workbook open command in there !!!!

Thank you for your patience with me

 

[VoG]

Maybe

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(varsheet)
            .Copy
            ActiveWorkbook.SaveAs Filename:="O:\WeatherReport" & .Name & VBA.Format(Date, "mmddyyyy") & ".xls"
        End With
    End If
Next i

 

[juan4412]

That opens it, but still the subscript out of range on the wb.sheets statement.