Excel Macro: make a recorded one less cell specifc?

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

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)

You can actually do the individual steps you're asking about all in one swift move. To make it go to the last cell in the current row, you just use ActiveCell.End(xlToRight). (Use 'ActiveCell' because it is equivalent to 'Selection' when only one cell is selected, but works even if multiple cells are selected.)

Range(ActiveCell, ActiveCell.Offset(0, -3)).Select

will select the current cell and 3 more to the left. Note that you do not need to do "Selection.Copy" in a separate step. You can simply go:

Range(ActiveCell, ActiveCell.Offset(0, -3)).Copy