Excel Macro: make a recorded one less cell specifc?

Question

I've recorded a simple macro that I need to make into something generic, so it can be used for any row and last four cells. Here is my recroded version:

Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToLeft).Select
Range("Q12:T12").Select
Range("T12").Activate
Selection.Copy

End Sub

How do I

make it go to the last cell of the ROW I place the cursor into?
change the cell specific ranges into a range that just means: select this cell and 3 more to the left?

... the selection.copy I think I can nearly manage :)

Many thanks Mike

PRO TIP: If you're using Excel for Windows, you can record your macros in RELATIVE mode instead of ABSOLUTE mode and get a recording in which all cell references are relevant to the starting selection instead of absolute. — Joel Spolsky

Alex P Alex P · Accepted Answer · 2010-12-08T13:19:38

This will copy the last four cells in any row you click into:

Sub CopyLastFourCellsOfRow()
Dim lastCell As Range
Dim rngToCopy As Range

Set lastCell = Selection.End(xlToRight)
Set rngToCopy = Range(lastCell, lastCell.Offset(0, -3))

rngToCopy.Copy

End Sub

Update - If your row has broken data then best approach is to start in the final column of the spreadsheet (column IV) and then work back. To achieve this replace the lastCell statement with the following:

Set lastCell = Cells(Selection.Row, 256).End(xlToLeft)

Excel Macro: make a recorded one less cell specifc?

2 Answers