How Do You Copy Range of Cells Until Last Row from Another Workbook in Excel VBA

Question

I am attempting to copy a range of cells until the last used row from another workbook that my main workbook has already opened and activated. The range needs to begin at C2 over to Column M and then until the first blank row.

The Code I have so far copies the right cell columns, but extends down past the used rows into blank rows. I've attached a screenshot. Pasting the copied cells into the main workbook is successful.

Workbooks.Open ("C:\Users\user\Documents\Maintenance Department\General\Equipment Documentation\Holding Furnace\Readings\Data Logs\Flow Sensor Monthly Alarm Log - Inductor.xlsx")
ThisWorkbook.Activate

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

Workbooks("Flow Sensor Monthly Alarm Log - Inductor.xlsx").Worksheets("Sheet1").Range("C2:M2" & LastRow).Copy

ThisWorkbook.Sheets("Data").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

That is because you are getting the last row from the worksheet to which you are copying not the workbook that just got opened: LastRow = Workbooks("Flow Sensor Monthly Alarm Log - Inductor.xlsx").Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row — Scott Craner
I replaced the line with the code in your reply and the copied cells extend down to row 26 now instead of row 21 — Chris R
Also Range("C2:M2" & LastRow) will return the wrong row, if LastRow is 6 then the range becomes Range("C2:M26") and not Range("C2:M6") You want Range("C2:M" & LastRow) — Scott Craner

Shai Rado Shai Rado · Accepted Answer · 2019-01-14T22:59:49

Try using Workbook and Worksheet object instead of Activate :

Dim WB As Workbook
Dim DestSht As Worksheet
Dim SourceSht As Worksheet

Set DestSht = ThisWorkbook.Worksheets("Data")

Set WB = Workbooks.Open("C:\Users\user\Documents\Maintenance Department\General\Equipment Documentation\Holding Furnace\Readings\Data Logs\Flow Sensor Monthly Alarm Log - Inductor.xlsx")
Set SourceSht = WB.Worksheets("Sheet1")

LastRow = SourceSht.Cells(Rows.Count, "A").End(xlUp).Row

SourceSht.Range("C2:M" & LastRow).Copy
DestSht.Range("C" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

How Do You Copy Range of Cells Until Last Row from Another Workbook in Excel VBA

1 Answers