VBA to Copy Data From Other Workbooks Suddenly Fails

Hello all,

I found a solution on this site to a problem I was having in excel. A few small changes were needed, but then it worked flawlessly. After several times of running the code, I tried again today, but now it doesn't add any new information. It runs the script, no errors, and then doesn't paste any data. I have no idea why it has randomly stopped working.

Code:

---

Sub BulkImport()

  Dim InFileNames As Variant
  Dim fCtr As Long
  Dim tempWkbk As Workbook
  Dim consWks As Worksheet
  Set consWks = ThisWorkbook.Sheets(1)
  InFileNames = Application.GetOpenFilename _
  (FileFilter:="Excel Files, *.*", MultiSelect:=True)
  Application.ScreenUpdating = False
  If IsArray(InFileNames) Then
      For fCtr = LBound(InFileNames) To UBound(InFileNames)
        With Workbooks.Open(Filename:=InFileNames(fCtr))
            .Sheets(1).Range("A2:I" & .Sheets(1).Range("A" & .Sheets(1).Rows.Count).End(xlUp).Row).Copy consWks.Range("A" & consWks.Rows.Count).End(xlUp)(2)
            .Close 0
        End With
      Next fCtr
  Else
      MsgBox "No file selected"
  End If
  With Application
      .StatusBar = True
      .ScreenUpdating = True
  End With
 
End Sub

---

Any ideas?

Thanks,

Amit

It's working fine.

can you add this line after With Workbooks.Open...

Code:

---

MsgBox "Number of cells that have data: " & Application.WorksheetFunction.CountA(.Sheets(1).Range("A2:I" & .Sheets(1).Range("A" & .Sheets(1).Rows.Count).End(xlUp).Row))

---

and let us know what the msgbox returns.

Hello,

I added the line you provided and the output was "Number of cells that have data: 27326"

The code looks fine. Can you please attach the workbooks here ?

Interestingly enough, I copied the module from the personal workbook (all workbooks) to the specific workbook that will be using this exclusively, and it worked again. I'm not sure why though.

It's got to do with the difference between ActiveWorkbook and ThisWorkbook.

ThisWorkbook always refers to the workbook file wherein the code is running from. If you change ThisWorkbook, to ActiveWorkbook, the code should run from the personal.xlsb file too.

ThisWorkBook object refers to the workbook that the code is contained in. ActiveWorkBook object refers to the workbook that is currently active.

Most times, they will refer to the same workbook. But if the workbook running the code is not the active workbook then they will point to different objects.

Thank you very much guys. Your prompt and helpful responses helped immensely. I'm still new to all this and I appreciate all your help.

I'm not sure how to set this thread as [SOLVED], unless that's not needed.

- Amit