Insert copied row below selected row

satheo

New Member
Joined
Jun 10, 2014
Messages
29
Trying to make a macro that inserts a copied row from sheet 2 below the selected row in sheet 1. I'm not sure about formatting but so far I have:

VBA Code:
Sub AddRow()
    
    Sheets("Sheet1").Range("A9:BU9").Copy  'this is what I want to copy, will be same row every time
    Sheets("Sheet2").Range(ActiveCell.Row).Select  'where I need help, want it to insert the copied row based on selection
    Selection.Insert Shift:=xlDown

    Application.CutCopyMode = False     'clears clipboard cause i have some other macros that would get messed up 

End Sub
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub AddRow()
    
    Sheets("Sheet1").Range("A9:BU9").Copy  'this is what I want to copy, will be same row every time
    Sheets("Sheet2").Range("A" & ActiveCell.Row).insert 'where I need help, want it to insert the copied row based on selection
    

    Application.CutCopyMode = False     'clears clipboard cause i have some other macros that would get messed up

End Sub
 
Solution

satheo

New Member
Joined
Jun 10, 2014
Messages
29
How about
VBA Code:
Sub AddRow()
   
    Sheets("Sheet1").Range("A9:BU9").Copy  'this is what I want to copy, will be same row every time
    Sheets("Sheet2").Range("A" & ActiveCell.Row).insert 'where I need help, want it to insert the copied row based on selection
   

    Application.CutCopyMode = False     'clears clipboard cause i have some other macros that would get messed up

End Sub
Thank you, this seems to work but it inserts the row above the selected one, can it be adjusted to insert it below?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
Sheets("Sheet2").Range("A" & ActiveCell.Row+1).Insert
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,682
Members
415,920
Latest member
ExcelNoob28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top