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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
To use the macro in post #17 :
• For the first input box, select the required rows on the source sheet and click OK.
• For the second input box, go to Sheet1, select the required row and click OK.

To use the macro in post #19:
• For the first input box, select the required rows on the source sheet and click OK.
• For the second input box, select the required destination row on the source sheet and click OK. The rows per the first input box will be inserted on Sheet1 at the row selected in the second input box.

If you want to type the destination row (instead of selecting) into the second input box :
VBA Code:
Sub v()
Dim c As Range, d, a As Range
On Error Resume Next
Set c = Application.InputBox("Select the row(s) to be copied.", Type:=8)
d = Application.InputBox("Enter the row number where to insert.", Type:=1)
On Error GoTo 0
If c Is Nothing Then
    MsgBox "The source rows were not selected"
    Exit Sub
ElseIf TypeName(d) = "Boolean" Then
    MsgBox "The destination row was not entered"
    Exit Sub
End If
For Each a In c.Areas
    a.EntireRow.Copy
    Sheets("Sheet1").Rows(Val(d)).EntireRow.Insert
Next
Application.CutCopyMode = False
End Sub
 
Upvote 1
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
 
Upvote 0
Solution
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Does the suggested macro do what you want?
 
Upvote 0

Forum statistics

Threads
1,215,350
Messages
6,124,439
Members
449,160
Latest member
nikijon

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
Back
Top