Excel – Copy Above Row to Multiple Rows Below

If you are like me and type a lot of data in Excel, you might sometimes need to copy part of a row of data to one or more of the rows below.

For copying a row to the first empty row below it, you select the cells you want your data copied copied to on the first empty row and press CTRL+D and the above values will be pasted to the new row.

But if you have the same data on multiple empty rows below?

Sure, you can select the required data, copy it, select the desired rows and paste it. But that’s a lot of moves and when you do this a lot of times it gets annoying.

So, make a button on your Quick Access Toolbar or on a Ribbon for the code below:

Sub Copy_row_above_to_selection()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim first_col_letter, last_col_letter As String
Dim first_cell_row As Integer
Dim rng_paste As Range

Set rng_paste = Selection
first_cell_row = Selection.Row

‘ letter of first column in selection
first_col_letter = Split(Cells(Selection.Row, Selection.Column).Address, “$”)(1)
‘ letter of last column in selection
last_col_letter = Split(Cells(Selection.Row, Selection.Column + rng_paste.Columns.Count – 1).Address, “$”)(1)

‘ copy row above selection
ActiveSheet.Range(first_col_letter & Selection.Row – 1 & “:” & last_col_letter & Selection.Row – 1).Copy
‘ and paste it to selection
rng_paste.PasteSpecial xlPasteAll

Application.CutCopyMode = False
ActiveSheet.Range(first_col_letter & first_cell_row – 1).Activate

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

How to use it?

Select the rows you want your data pasted into and click the button:

It essentially does the select+copy / select+paste action for you in one go.