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).Ra nge("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