VBA to Copy Data From Other Workbooks Suddenly Fails

[ghendi]

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.

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

[Admin]

It's working fine.

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

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.

[ghendi]

Hello,

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

[Admin]

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

[ghendi]

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.

[Excel Fox]

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.

[ghendi]

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