Pause a macro to select two consecutive cells, copy them and prompt to select a new location where the macro will then paste the values

Saltysteve

New Member
Joined
Jul 23, 2014
Messages
36
Office Version
  1. 365
Platform
  1. Windows
Hi all,
I am trying to get a macro to insert a row at a selected position then pause and allow me to select two consecutive cells from another location which the macro then copies and pastes the values into columns D and E of the newly inserted row.
I sort of have some code that semi works except it only copies and pastes the first cell.
Any assistance will be appreciated.

Sub InsertRow()

'Declare variables
Dim Rng As Range

ScreenUpdating = False

'Insert row
Selection.Resize(rowsize:=2).Rows(2).EntireRow.Resize(rowsize:=1).Insert Shift:=xlDown

On Error Resume Next
Set Rng = Application.InputBox("Select the 2 cells to be copied", Type:=8) ' This only copies the first cell, even though 2 cells are selected

SO = Rng(1).Value 'Selection.Copy does copy both cells but not sure how to get it into the inserted row

ActiveCell.Offset(1, 0).Value = SO ' the active cell is the one above the inserted row in column D. I need to move down 1, select the cells in D and E and paste the data

ScreenUpdating = True

End Sub
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

QuietRiot

Well-known Member
Joined
May 18, 2007
Messages
1,077
seems kind of risky if you don't put checks in place so only 2 cells are being copied..
VBA Code:
Rng.Copy
ActiveCell.Offset(1, 0).PasteSpecial
 

Watch MrExcel Video

Forum statistics

Threads
1,127,318
Messages
5,623,974
Members
416,002
Latest member
Neshx

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