Macro needed for transferring rows to new worksheet

[Fraserj]

Hi, I'm struggling with Macros abit and I am wondering if any of you people could help me?

I have 2 worksheets "Sheet1" & "Sheet2"

I have done a small table below just as an example.
Worksheet one is the sheet that I fill in.. I need a Macro that automatically transfers a whole row once any cell in Row "R" has "COMPLETED" in the cell.
I want this row to be Cut and Pasted into the next available Row in Sheet2.
Once the Row is Transferred I need the other Rows in "Sheet1" to move up, rather than leaving an empty row at the top of the work sheet.
[TABLE="width: 500"]
<tbody>[TR]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]File Sent Back[/TD]
[TD]Reference Number[/TD]
[TD]Complete?[/TD]
[/TR]
[TR]
[TD]Yes[/TD]
[TD]dadkslmf[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yes[/TD]
[TD]fjdfn[/TD]
[TD]COMPLETED[/TD]
[/TR]
[TR]
[TD]Yes[/TD]
[TD]kjfds[/TD]
[TD]COMPLETED[/TD]
[/TR]
</tbody>[/TABLE]

Is this possible, if so can somebody help me?

Also If it is possible, is it also possible to undo the Macro if COMPLETED is typed by mistake, or in the wrong Row, so it will transfer back to worksheet 1

 

[My Aswer Is This]

No if you had the macro delete the row you cannot undo that action.
I normally suggest we you a double click event. That means in you double click on any row in column "R" it will copy over your data and then delete the row. It's hard to accidently double click a cell.
Would that option work for you?

 

[My Aswer Is This]

If your willing to use my suggestion. Any time you double click on a cell in column "R" this row of data will be copied over to sheet(2) on the first empty row. And then the script will delete that row on sheet(1)
Be sure and note Sheet(1) is the sheet in the far left position on your tab menu and sheet (2) is the next sheet to the right on the tab bar.
To install this code:

Right-click on the sheet (1) tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Not Intersect(Target, Range("R:R")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Lastrow = Sheets(2).Cells(Rows.Count, "R").End(xlUp).Row + 1
Rows(Target.Row).Copy Destination:=Sheets(2).Rows(Lastrow)
Rows(Target.Row).EntireRow.Delete
End If
End Sub

 

[Fraserj]

If your willing to use my suggestion. Any time you double click on a cell in column "R" this row of data will be copied over to sheet(2) on the first empty row. And then the script will delete that row on sheet(1)
Be sure and note Sheet(1) is the sheet in the far left position on your tab menu and sheet (2) is the next sheet to the right on the tab bar.
To install this code:

Right-click on the sheet (1) tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Not Intersect(Target, Range("R:R")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Lastrow = Sheets(2).Cells(Rows.Count, "R").End(xlUp).Row + 1
Rows(Target.Row).Copy Destination:=Sheets(2).Rows(Lastrow)
Rows(Target.Row).EntireRow.Delete
End If
End Sub

That worked perfect... Double clicking doesn't bother me, I've set a conditional formatting on the box to turn green so it gives an indication that it should be double click anyway
Thank you

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

That worked perfect... Double clicking doesn't bother me, I've set a conditional formatting on the box to turn green so it gives an indication that it should be double click anyway
Thank you