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 · Accepted Answer · 2013-01-26 02:03:57Z

3

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

edited Jan 26, 2013 at 2:03

answered Jan 25, 2013 at 5:46

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Prash Over a year ago

I used this but it will copy the data from the activesheet each time sheet does not exist. I want it to skip copying and move to next sheet.

peterm Over a year ago

When a sheet doesn't exist ws will be evaluated as Nothing and thus your whole If Not ws Is Nothing Then ... End If block shouldn't execute

Prash Over a year ago

Thanks Peter, Whats the way to ws not evaluated and move to next ws

Prash Over a year ago

This will skip only once. For example there are 6 sheets in array but in workbook only 3 exist. Using this code will skip only for first sheet and it throws error for next sheet which does not exist.

Doug Glancy Over a year ago

I know you found something that works, but I think this answer is overly complicated and that your first version was closer to the ideal. The thing to realize is that the attempt to set ws to a non-existent worksheet leaves it set to whatever it was set to before. It doesn't set it to Nothing. However, if you set it to Nothing before attempting to set it to a member of the array, you can then easily test if it's still Nothing and just skip the processing. I'm always leery of code where my On Error pairs are at different levels of indentation :).

|

Collectives™ on Stack Overflow

VBA code to ignore error if sheet from array not exist

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related