VBA code to ignore error if sheet from array not exist

Question

I am trying to combine specific sheets to one sheet from workbook. Challenge here is sheets from array might not be available all the time. so the macro should ignore those and move to next sheet to copy data. I have written code but macro throes error when sheet does not exist.

Sub test()
Dim MyArr, j As Long
Dim ws As Worksheet
Dim sary, i As Long

Worksheets.Add Before:=Worksheets("Equity")
ActiveSheet.Name = "Consolidated"
MyArr = Array("Sample Sheet_Equity", "Sample Sheet_Funds", "Sample Sheet_Warrants",    "Eq", "Fu", "Wa")

For j = 0 To UBound(MyArr)

Set ws = Worksheets(MyArr(j))

If Not ws Is Nothing Then

    ws.Select
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Consolidated").Select
    Range("A2").End(xlDown).Offset(1, 0).Select

    ActiveSheet.Paste
End If
Next
End Sub

peterm peterm · Accepted Answer · 2013-01-25T05:46:26

You can do it like this:

For j = 0 To UBound(MyArr)
    On Error Resume Next
    Set ws = Worksheets(MyArr(j))
    If Err.Number = 0 Then
        On Error GoTo 0    
        If Not ws Is Nothing Then
            'Your copying code goes here
        End If
    Else
        Err.Clear
    End If
Next

UPDATE: Thanks to Doug Glancy's comment here is more streamlined version

For j = 0 To UBound(MyArr)
    Set ws = Nothing

    On Error Resume Next
    Set ws = Worksheets(MyArr(j))
    On Error GoTo 0    

    If Not ws Is Nothing Then
        'Your copying code goes here
    End If
Next

VBA code to ignore error if sheet from array not exist

1 Answers