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.

excel_zsbb6w3f08

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:

excel_hmiizdoftg

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

You might also like

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More