Copy data up to last row and column

Question

I am trying to copy all the data from multiple workbooks to a single workbook and distribute them to different worksheets.

How should I change the code below to automatically select the data up to the last row and column with value and copy them instead of declaring the range?

For i = 16 To ws.Range("I" & Rows.Count).End(xlUp).Row 'Sheet1 is MasterSheet
    File = ws.Range("C" & i) & ws.Range("I" & i) 'File Location and Excel Name
    Copy = ws.Range("U" & i) & ":" & ws.Range("V" & i) 'Range to be copied
    Workbooks.Open File, 0, 1 'Open File as Read Only
    Range(Copy).Copy
    ThisWorkbook.Sheets(ws.Range("W" & i).Value).Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial 12 'Paste as Values only
    ActiveWorkbook.Close False 'Close WorkBook without saving
    Selection.Value = Selection.FormulaR1C1 'Force F2 and Enter selected range

    Range("A1").Select

Next i

Neoromanzer Neoromanzer · Accepted Answer · 2017-10-26T17:44:35

Your solution works fine, Paolo. I did quite a bit of research on the topic some time ago and I found out these ways are the fastest and simplest ones. I don't think Excel is very good with these things, so I have always tried to stick to the basics.

Just two comments:

I would not use .UsedRange.Copy, since it will return the range of cells that have ever been used.
I would also try to avoid using.select as much as possible. You can find examples on how to avoid it in How to avoid using Select in Excel VBA

Copy data up to last row and column

1 Answers