error handler works only once in vba

Question

I am trying to pull data from several workbooks which have different sheet names. I have created an array which contains all the possible sheet names. When data workbook opens and sheet name is not found the error handler works for the first time when loop runs again and pull the next array element, error handler doesn't work. It gives "Subscript out of range" error. Can anyone please elaborate what am I missing here? What I want is in case consecutive sheet names are not available in data workbook, code should go into for loop again and search for next sheet name.

Public strFileName As String
Public currentWB As Workbook
Public dataWB As Workbook

Sub GetData()
    Dim strListSheet As String
    Dim i As Integer
    Dim VendorValue As String
    Dim SheetNames() As Variant
    Dim a As String

    strListSheet = "Master"

    Sheets(strListSheet).Select
    Range("First_file").Select
    SheetNames = Range("Sheet_Names")

    'this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
    Set currentWB = ActiveWorkbook
    Do While ActiveCell.Value <> ""

        strFileName = ActiveCell.Offset(0, 1) & ActiveCell.Value
        VendorValue = ActiveCell.Offset(0, 2)
        Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True
        Set dataWB = ActiveWorkbook

        For i = LBound(SheetNames, 1) To UBound(SheetNames, 1)
        a = SheetNames(i, 1)
        b = SheetNames(i, 2)

        dataWB.Activate
        On Error GoTo Handler:
        ActiveWorkbook.Sheets(a).Select

        Range("H5:H120,I5:I120,M5:M120,P5:P120,U5:X120").Select
        Selection.Copy

        currentWB.Activate
        Sheets(VendorValue).Select
        Range(b).Select

        Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
        Application.CutCopyMode = False

Handler:
        Next  
        dataWB.Close False
        Sheets(strListSheet).Select
        ActiveCell.Offset(1, 0).Select

    Loop

    Exit Sub
End Sub

Answer 1

If all your files are in the same path, I think it's easier to use this:

Sub openOtherWorkbooks()

    Dim folderPath As String, path As String

    folderPath = "C:\Path\to\your\files"

    path = folderPath & "\*.xlsm"        'xlsm as an example - could be xls* as well

    Do While Filename <> ""

        Filename = Dir()

        If Filename <> ThisWorkbook.Name And Filename <> "" Then

            Workbooks.Open folderPath & "\" & Filename

            For i = 1 To Workbooks(Filename).Sheets.count

                ' do everything with every sheet of this file

            Next i

            Workbooks(Filename).Close False

        End If

        Filename = Dir(path)

    Loop

End Sub

It's just opening every file, counting the sheets (beginning with 1) of the opened file and then there should be your code.

It's not exactly an answer to your On-Error-GoTo-thing with your handler.

Answer 2

I'd change approach like follows:

Dim mySht as Worksheet 

a = SheetNames(i, 1)
Set mySht = GetSheet(dataWB, a)
If Not mySht Is Nothing Then
   b = SheetNames(i, 2)
   With mySht
      .Range("H5:H120,I5:I120,M5:M120,P5:P120,U5:X120").Copy
      currentWB.Sheets(VendorValue).Range(b).PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
        Application.CutCopyMode = False
   End With  
End If

Where I only showed the part that goes from a and b settings (included) to the Handler label (included, i.e. it has to disappear).

And you have to put this code in any module (also at the end of your Sub will do):

Function GetSheet(wb as Workbook, shtName as String)
   On Error Resume Next
   Set GetSheet = wb.Worksheet(shtName)
End Function

Finally the rest of your code can avoid a lot of Activate/Active/Select/Selection stuff in a similar manner

Answer 3

You have to exit the error handler in order to reuse it. That is you need a Resume clause at the end of your error handler.

Check this site for more details.

I have moved the handler at the end of the sub and added a Resume.

Public strFileName As String
Public currentWB As Workbook
Public dataWB As Workbook

Sub GetData()
    Dim strListSheet As String
    Dim i As Integer
    Dim VendorValue As String
    Dim SheetNames() As Variant
    Dim a As String

    strListSheet = "Master"

    Sheets(strListSheet).Select
    Range("First_file").Select
    SheetNames = Range("Sheet_Names")

    'this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
    Set currentWB = ActiveWorkbook
    Do While ActiveCell.Value <> ""

        strFileName = ActiveCell.Offset(0, 1) & ActiveCell.Value
        VendorValue = ActiveCell.Offset(0, 2)
        Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True
        Set dataWB = ActiveWorkbook

        For i = LBound(SheetNames, 1) To UBound(SheetNames, 1)
        a = SheetNames(i, 1)
        b = SheetNames(i, 2)

        dataWB.Activate
        On Error GoTo Handler:
        ActiveWorkbook.Sheets(a).Select

        Range("H5:H120,I5:I120,M5:M120,P5:P120,U5:X120").Select
        Selection.Copy

        currentWB.Activate
        Sheets(VendorValue).Select
        Range(b).Select

        Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
        Application.CutCopyMode = False

Handler2:
        Next  
        dataWB.Close False
        Sheets(strListSheet).Select
        ActiveCell.Offset(1, 0).Select

    Loop

    Exit Sub
Handler:
    Resume Handler2
End Sub