Copy Row to new sheet.

[blee4372]

I have a worksheet called "Tracker" & what I am trying to do is...If any date is entered in column M that row is copy to the next blank row on a new worksheet called "Completed Orders". Also would like the macro to delete the row from the "Tracker" sheet.
I would be greatful for any help.

 

[My Aswer Is This]

You said:

If any date is entered in column M​

So it must be a Date?

So if Apple is entered then nothing happens is that correct?

Is the date entered manually or as a result of a formula. Or copied in?

 

[My Aswer Is This]

I always believe it's safer to use a double click event
When you double click on a cell in column M this row of data will be copied to sheet named Completed Orders
And the row will then be deleted from original sheet.
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Put this script in the sheet named Tracker

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified 6/4/18 7:00 AM EDT
If Not Intersect(Target, Range("M:M")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Cancel = True
Dim r As Long
Dim Lastrow As Long
Lastrow = Sheets("Completed Orders").Cells(Rows.Count, "M").End(xlUp).Row + 1
r = Target.Row
Rows(r).Copy Sheets("Completed Orders").Rows(Lastrow)
Rows(r).Delete
End If
End Sub

 

[blee4372]

I always believe it's safer to use a double click event
When you double click on a cell in column M this row of data will be copied to sheet named Completed Orders
And the row will then be deleted from original sheet.
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Put this script in the sheet named Tracker

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified 6/4/18 7:00 AM EDT
If Not Intersect(Target, Range("M:M")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Cancel = True
Dim r As Long
Dim Lastrow As Long
Lastrow = Sheets("Completed Orders").Cells(Rows.Count, "M").End(xlUp).Row + 1
r = Target.Row
Rows(r).Copy Sheets("Completed Orders").Rows(Lastrow)
Rows(r).Delete
End If
End Sub

That works...Thanks you....I more question? Is it possible for r to equal a target range (D:M)

 

[My Aswer Is This]

You said in your original post:

that ​

row​

is copy to the next blank row on a new worksheet called "Completed Orders

So are you now saying you only want:

D:M​

copied to other sheet?

And if so where on other sheet will it be pasted?

Column D:M ?_{^{<strike>
</strike>}}​

 

[blee4372]

You said in your original post:

that ​

row​

is copy to the next blank row on a new worksheet called "Completed Orders

So are you now saying you only want:

D:M​

copied to other sheet?

And if so where on other sheet will it be pasted?

Column D:M ?_{^{<strike>
</strike>}}​

Sorry for the confusion...This macro works perfectly for this worksheet...I was just thinking if I applied this macro to other worksheets in the future, If it was possible to do a range....& it would be pasted on the new sheet starting at column A.

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified 6/4/18 8:45 AM EDT
If Not Intersect(Target, Range("M:M")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Cancel = True
Dim r As Long
Dim Lastrow As Long
Lastrow = Sheets("Completed Orders").Cells(Rows.Count, "A").End(xlUp).Row + 1
r = Target.Row
Cells(r, "D").Resize(, 10).Copy Sheets("Completed Orders").Cells(Lastrow, 1)
Rows(r).Delete
End If
End Sub

 

[blee4372]

Try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified 6/4/18 8:45 AM EDT
If Not Intersect(Target, Range("M:M")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Cancel = True
Dim r As Long
Dim Lastrow As Long
Lastrow = Sheets("Completed Orders").Cells(Rows.Count, "A").End(xlUp).Row + 1
r = Target.Row
Cells(r, "D").Resize(, 10).Copy Sheets("Completed Orders").Cells(Lastrow, 1)
Rows(r).Delete
End If
End Sub

Thanks again for all your help...works great.

 

[My Aswer Is This]

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

Thanks again for all your help...works great.