Macro to copy row and paste row in a different sheet based on user input

[Buns1976]

Hi Everyone,

How would one modify the code below to paste to a row in a different sheet for example copy Row 2 in Sheet "Cost"
and Paste/Insert in Row 5 in Sheet "Profit"?

Thank you,

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

 

[rollis13]

Change these lines from:

Rows(Val(c)).Copy
Rows(Val(d)).Insert

to this:

Sheets("Cost").Rows(Val(c)).Copy
Sheets("Profit").Rows(Val(d)).Insert

 

[Buns1976]

Change these lines from:

Rows(Val(c)).Copy
Rows(Val(d)).Insert

to this:

Sheets("Cost").Rows(Val(c)).Copy
Sheets("Profit").Rows(Val(d)).Insert

Good Lord. Don't know why I didn't see that? THANK YOU VERY MUCH!

 

[rollis13]

Glad having been of some help.

 

[Buns1976]

Change these lines from:

Rows(Val(c)).Copy
Rows(Val(d)).Insert

to this:

Sheets("Cost").Rows(Val(c)).Copy
Sheets("Profit").Rows(Val(d)).Insert

Wondering if there is a way to select multiple rows rather that just a single row? Maybe something like "Start Row, End Row"?

 

[rollis13]

Have a try, added some error-checking but no sure it's bullet-proof (if necessary you may add/fix that ).

Sub v()
    Dim c, d
    Dim flRow() As String
    Dim fRow, lRow
    c = Application.InputBox("Enter 'row number' to be copied." & vbLf & "For multiple rows use format 'first-last'.", "Input Row Copy")
    If TypeName(c) = "Boolean" Then Exit Sub
    flRow = Split(c, "-")
    fRow = flRow(0)
    On Error Resume Next
    lRow = flRow(1)
    On Error GoTo 0
    If lRow = "" Then lRow = fRow
    d = Application.InputBox("Enter the row number where to insert.", "Destination Row")
    If TypeName(d) = "Boolean" Then Exit Sub
    Sheets("Cost").Range(Val(fRow) & ":" & Val(lRow)).EntireRow.Copy
    Sheets("Profit").Rows(Val(d)).Insert
    Application.CutCopyMode = False
    Exit Sub
End Sub

 

[Buns1976]

Have a try, added some error-checking but no sure it's bullet-proof (if necessary you may add/fix that ).

Sub v()
    Dim c, d
    Dim flRow() As String
    Dim fRow, lRow
    c = Application.InputBox("Enter 'row number' to be copied." & vbLf & "For multiple rows use format 'first-last'.", "Input Row Copy")
    If TypeName(c) = "Boolean" Then Exit Sub
    flRow = Split(c, "-")
    fRow = flRow(0)
    On Error Resume Next
    lRow = flRow(1)
    On Error GoTo 0
    If lRow = "" Then lRow = fRow
    d = Application.InputBox("Enter the row number where to insert.", "Destination Row")
    If TypeName(d) = "Boolean" Then Exit Sub
    Sheets("Cost").Range(Val(fRow) & ":" & Val(lRow)).EntireRow.Copy
    Sheets("Profit").Rows(Val(d)).Insert
    Application.CutCopyMode = False
    Exit Sub
End Sub

PERFECT. Thank you kindly!

 

[rollis13]

Thanks for the positive feedback.