Copy and Paste Macro to specific Column Ranges

Question

I've created a macro in Excel to copy a row in one of my sheets and insert the row into another sheet above the user's selected row. The macro is as follows and works correctly:

Sub addTestProductRow()
    Sheets("Macro templates").Range("B2:J2").Copy
    Selection.Insert Shift:=xlDown
End Sub

However, I wish to add to this macro to make it only insert in the columns B to J, regardless of which cell the user has selected. I still wish for it to insert above the users selected row, just in the specified column range.

How is the user calling the function? Do they select the cells and then click a button? You can extract just the row of the selected worksheet and use the information to generate your copy range. Is this what you are thinking? — nbayly
The user selects a cell and presses the hotkey (ctrl-i) and this will call the function and insert the row (with column ranges B - J) and paste it above the cell the user has selected. The problem is, that if the user doesn't have a cell in column B selected the row is inserted and the formatting is ruined as the row doesn't line up with columns B to J — joshmccure

Scott Craner Scott Craner · Accepted Answer · 2015-11-23T23:13:30

This should do what you want:

Sub addTestProductRow()
    Dim t As Long
    t = Selection.Row
    Rows(t).Insert
    Sheets("Macro templates").Range("B2:J2").Copy
    Range("B" & t).PasteSpecial

End Sub

Copy and Paste Macro to specific Column Ranges

1 Answers