Copy the last 5 cells in a column until last row

Question

I have a text based system with people's names, and I need to copy and paste the first 4 cells (not a problem) and the last 5 cells to a different worksheet for analysis.

The problem arises with the transfer from the text based system the data is presented in, to the spreadsheet when it comes to people with spaces in their surname (ie, Da Silva). I use text to columns, which will give me a variant number columns, depending on the number of spaces in the name which is an issue.

I already have a crude solution, but the time it takes and the jumping about between screens while the macro is running looks very unprofessional. I don't get much spare time at work, and I don't have the tools to test this properly at home.

Can anyone help me get this to loop until the last empty cell, and basically neaten it up a little?

The code, which repeats 300 times, is as following:

Sheets("Late list Sorting").Select
Range("A2").End(xlToRight).Select
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, -5)).Select
Selection.Copy
Sheets("Late list").Select
Range("D4").Select
ActiveSheet.Paste

(...Repeat until...)

Range("A300").End(xlToRight).Select
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, -5)).Select
Selection.Copy
Sheets("Late list").Select
Range("D302").Select
ActiveSheet.Paste
Sheets("Late list Sorting").Select

Could you please provide a picture or the expected result? – Error 1004 May 22 '19 at 12:05 — Error 1004, May 22 '19 at 12:05

Steven Carlson · Answer 1 · 2019-05-22T12:06:39.257

0

it is inefficient to use .Select, here's a great resource: How to avoid using Select in Excel VBA

To eliminate the screen flashes, put this at the beginning of your code. Remember to turn it back to true at the end.

Application.ScreenUpdating = False

You want to use a loop, where the variable i becomes the row number you will reference like so:

 Range("A" & i).Value...

There are many ways to loop, here is one example:

For i = 1 to 300
    //All your code here
Next i

edited May 22 '19 at 12:06

answered May 22 '19 at 12:03

Steven Carlson

83
5

Thank you for your reply, that ScreenUpdating tip is really beneficial. – Conor May 22 '19 at 14:10

score 0 · Accepted Answer · answered May 22 '19 at 12:25

If you only want the values (not formulae or formatting) then you can simply use Value=Value...

Dim i As Long, sWs As Excel.Worksheet, tWs As Excel.Worksheet
Set sWs = ThisWorkbook.Worksheets("Late list Sorting")
Set tWs = ThisWorkbook.Worksheets("Late list")
For i = 2 To 300
    tWs.Range(tWs.Cells(i + 2, 4), tWs.Cells(i + 2, 8)).Value = sWs.Range(sWs.Cells(i, 1).End(xlToRight), sWs.Cells(i, 1).End(xlToRight).Offset(0, -5)).Value
Next

Copy the last 5 cells in a column until last row

2 Answers2