Macro is copy row and paste row based on user input

webbooo

New Member
Joined
Nov 8, 2011
Messages
19
Hello,

I currently have a macro that I recorded that basically copies row 15 and the inserts the copied row to row 18.
While this works ok, over time the number of rows will increase as such that row 15 will be a row that shouldnt be copied.
I'd like to like to look at having a macro that prompts the user which row number should be copied and then prompts for which row number it should be inserted into.

Any help would be appreciated.

Thanks
Adam
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,708
How does the user determine which rows they are? Might be a way to make that easier.
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,206
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
Sub v()
Dim c, d
c = Application.InputBox("Enter the row number to be copied.", Type:=1)
If TypeName(c) = "Boolean" Then Exit Sub
d = Application.InputBox("Enter the row number where to insert.", Type:=1)
If TypeName(c) = "Boolean" Then Exit Sub
Rows(Val(c)).Copy
Rows(Val(d)).Insert
Application.CutCopyMode = False
End Sub
 
Solution

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,708
Personally a quicker option.

Assign this to a shortcut (ctrl+shift+v perhaps), copy the row, select the location and use the shortcut

VBA Code:
Sub v()

Rows(ActiveCell.Row).Insert

End Sub
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,206
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Personally a quicker option.

Assign this to a shortcut (ctrl+shift+v perhaps), copy the row, select the location and use the shortcut

VBA Code:
Sub v()

Rows(ActiveCell.Row).Insert

End Sub
The copy/paste might be part of a larger macro.
Otherwise, it's just as easy to do a manual copy/paste.
 

webbooo

New Member
Joined
Nov 8, 2011
Messages
19

ADVERTISEMENT

VBA Code:
Sub v()
Dim c, d
c = Application.InputBox("Enter the row number to be copied.", Type:=1)
If TypeName(c) = "Boolean" Then Exit Sub
d = Application.InputBox("Enter the row number where to insert.", Type:=1)
If TypeName(c) = "Boolean" Then Exit Sub
Rows(Val(c)).Copy
Rows(Val(d)).Insert
Application.CutCopyMode = False
End Sub
I'd like to simplify as much as possible as the users of the spreadsheet are not experienced with excel. If they can simply enter the row number to copy and then enter the row number to paste/insert it will make things easy. Doing it manually will create confusion as the use of the spreadsheet and number of rows grows.
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,206
Office Version
  1. 2016
Platform
  1. Windows
Does the suggested macro do what you want?
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,089
Members
415,875
Latest member
Tarali

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