Macro is copy row and paste row based on user input

[webbooo]

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

 

[footoo]

Instead of :

Rows(Val(d)).Insert

Try :

Sheets("Your sheet name").Rows(Val(d)).Insert

 

[billy1989]

This is almost perfect for my needs, but is there any way to amend it so that I can select multiple rows to copy?

 

[footoo]

Sub v()
Dim c As Range, d As Range
On Error Resume Next
Set c = Application.InputBox("Select the row(s) to be copied.", Type:=8)
Set d = Application.InputBox("Select the row where to insert.", Type:=8)
On Error GoTo 0
If c Is Nothing Then
    MsgBox "The source rows were not selected"
    Exit Sub
ElseIf d Is Nothing Then
    MsgBox "The destination row was not selected"
    Exit Sub
End If
c.EntireRow.Copy
d.EntireRow.Insert
Application.CutCopyMode = False
End Sub

 

[billy1989]

Sub v()
Dim c As Range, d As Range
On Error Resume Next
Set c = Application.InputBox("Select the row(s) to be copied.", Type:=8)
Set d = Application.InputBox("Select the row where to insert.", Type:=8)
On Error GoTo 0
If c Is Nothing Then
    MsgBox "The source rows were not selected"
    Exit Sub
ElseIf d Is Nothing Then
    MsgBox "The destination row was not selected"
    Exit Sub
End If
c.EntireRow.Copy
d.EntireRow.Insert
Application.CutCopyMode = False
End Sub

Apologies, I meant to quote the code I was using! I had amended slightly so that I could put them into another worksheet, so my code was as below

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
Sheets("Sheet1").Rows(Val(d)).Insert
Application.CutCopyMode = False
End Sub

 

[footoo]

The macro I posted should work. Have you tried it?

 

[billy1989]

The macro I posted should work. Have you tried it?

I selected multiple rows, it works if the rows selected are consecutive and I then go into the sheet to copy to and select the row, but it doesn't work if I select rows that aren't consecutive (as in image). Ideally I would need the rows inserted onto Sheet1 each time and I would just select the row to insert at as in the original code.

 

[footoo]

Sub v()
Dim c As Range, d As Range, a As Range
On Error Resume Next
Set c = Application.InputBox("Select the row(s) to be copied.", Type:=8)
Set d = Application.InputBox("Select the row where to insert.", Type:=8)
On Error GoTo 0
If c Is Nothing Then
    MsgBox "The source rows were not selected"
    Exit Sub
ElseIf d Is Nothing Then
    MsgBox "The destination row was not selected"
    Exit Sub
End If
For Each a In c.Areas
    a.EntireRow.Copy
    d.EntireRow.Insert
Next
Application.CutCopyMode = False
End Sub

 

[billy1989]

Sub v()
Dim c As Range, d As Range, a As Range
On Error Resume Next
Set c = Application.InputBox("Select the row(s) to be copied.", Type:=8)
Set d = Application.InputBox("Select the row where to insert.", Type:=8)
On Error GoTo 0
If c Is Nothing Then
    MsgBox "The source rows were not selected"
    Exit Sub
ElseIf d Is Nothing Then
    MsgBox "The destination row was not selected"
    Exit Sub
End If
For Each a In c.Areas
    a.EntireRow.Copy
    d.EntireRow.Insert
Next
Application.CutCopyMode = False
End Sub

Thank you - is there any way to set this so that it defaults to inserting on Sheet1 and I select the row number to insert on there, rather than having to reference the sheet in the input box?

 

[footoo]

You do not have to reference the sheet in the input box - go to Sheet1 and make the selection.
Or if you prefer to select on the active sheet :

Sub v()
Dim c As Range, d As Range, a As Range
On Error Resume Next
Set c = Application.InputBox("Select the row(s) to be copied.", Type:=8)
Set d = Application.InputBox("Select the row where to insert.", Type:=8)
On Error GoTo 0
If c Is Nothing Then
    MsgBox "The source rows were not selected"
    Exit Sub
ElseIf d Is Nothing Then
    MsgBox "The destination row was not selected"
    Exit Sub
End If
For Each a In c.Areas
    a.EntireRow.Copy
    Sheets("Sheet1").Rows(d.Row).EntireRow.Insert
Next
Application.CutCopyMode = False
End Sub

 

[billy1989]

You do not have to reference the sheet in the input box - go to Sheet1 and make the selection.
Or if you prefer to select on the active sheet :

Sub v()
Dim c As Range, d As Range, a As Range
On Error Resume Next
Set c = Application.InputBox("Select the row(s) to be copied.", Type:=8)
Set d = Application.InputBox("Select the row where to insert.", Type:=8)
On Error GoTo 0
If c Is Nothing Then
    MsgBox "The source rows were not selected"
    Exit Sub
ElseIf d Is Nothing Then
    MsgBox "The destination row was not selected"
    Exit Sub
End If
For Each a In c.Areas
    a.EntireRow.Copy
    Sheets("Sheet1").Rows(d.Row).EntireRow.Insert
Next
Application.CutCopyMode = False
End Sub

Sorry, what I'm looking for is for to be able to run the macro from a button on one sheet, to select the rows, but for these rows to insert by default onto Sheet1 at a row number I specify (eg as in images)