Hello,

I am running a looper. I have used this code for probably the last 10 years. All of a sudden it is throwing an automation error - exception occurred and then excel shuts down. I am running 64bit 2016 excel as of a few months ago. Any ideas if there is something in the code that is causing this?


Sub GetDataInput()
Dim I As Long
Dim J As Integer
Dim nRows As Long
Dim nDatarows As Long
Dim nLastRow As Long
Dim wks As Worksheet
Dim wksData As Worksheet
Dim strFileName As String
Dim wkbTarget As Workbook
Dim wksFileList As Worksheet

    Application.ScreenUpdating = False
    Set wksFileList = ThisWorkbook.Sheets("File List")
    Set wks = ThisWorkbook.Sheets("Extract Data")
    wks.Range("A12", "CC1000000").Clear
    nRows = wksFileList.Range("A1000000").End(xlUp).Row
    For I = 1 To nRows
        Application.StatusBar = "Processing File " & I & " of " & nRows & " Files"
        strFileName = wksFileList.Range("A" & I)
        Set wkbTarget = Workbooks.Open(strFileName, updatelinks:=0)
        wkbTarget.Sheets("Input").UsedRange.Copy
        nDatarows = wks.Range("A1000000").End(xlUp).Row + 1
        wks.Range("A" & nDatarows).PasteSpecial xlPasteValues
        nLastRow = wks.Range("A1000000").End(xlUp).Row
        For J = nDatarows To nLastRow
            wks.Range("c" & J) = strFileName
        Next J
        Application.CutCopyMode = False
        wkbTarget.Close savechanges:=False
        Set wkbTarget = Nothing
    Next I
    Application.StatusBar = False
    Application.ScreenUpdating = True
    MsgBox "Finished Processing"

End Sub