Moving row to another sheet based on cell yalue

[amburnham23]

I'm pretty clueless when it comes to coding and need some help with this excel file.

I'm trying to insert a code that will move an entire row from sheet "Ordered" to sheet "Invoiced" when yes is selected from the drop down list in column M.

I'd like it to not leave any blank lines on the "Ordered" sheet and fill into the next available line in the "Invoiced" sheet.

Any help is greatly appreciated and I can explain further if need be.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your "Ordered" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in column M.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("M:M")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target = "Yes" Then
        Target.EntireRow.Copy Sheets("Invoiced").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        Target.EntireRow.Delete
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[amburnham23]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your "Ordered" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in column M.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("M:M")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target = "Yes" Then
        Target.EntireRow.Copy Sheets("Invoiced").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        Target.EntireRow.Delete
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

This is great, thank you. Two quick things though, is there a way to adjust the code so it won't erase the line in the destination sheet? I would like it to go to the next available line rather than erase what is there. Also, how do I have multiple codes within the same sheet? I already have the following code to auto-generate the time and date and they don't seem to work well together.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rCell As Range
Dim rChange As Range

On Error GoTo ErrHandler
Set rChange = Intersect(Target, Range("B:B"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell > "" Then
With rCell.Offset(0, -1)
.Value = Now
.NumberFormat = "mmm d,yyyy h:mm AM/PM"
End With
Else
rCell.Offset(0, -1).Clear
End If
Next
End If

ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub

 

[mumps]

The code deletes the row in the "Ordered" sheet not the destination sheet which is the "Invoiced" sheet. If you don't want to delete the row in the "Ordered" sheet, just delete this line of code:

Target.EntireRow.Delete

You cannot have more than one Worksheet Change macro in the same code module so you have to combine the two macros into one. It would be easier for me to follow what you are trying to do if you could explain in detail how you want to auto-generate the time and date. It looks like you want to insert the time and date in column A when you insert data in column B. Also, if column B is blank, you want column A to be blank. Is this correct? Are you entering data in column B one cell at a time or are you pasting data into column B into more than one cell at a time? Please explain step-by-step using an example and referring to specific cells.

 

[amburnham23]

I'm trying to help keep order's for my wife's business organized, so column B is the Customer Name column so it gets entered one at a time. The time auto generates to help keep the sequence of orders and she knows how long people have been waiting, she can always filter that column to sort by time.

And I'd like it to still delete from the first sheet, but if there's already an order on the "Invoiced" sheet and I go to move a second order it deletes any order that was already there. I'd like it to paste into the next available row if possible.

Thanks for your help!

 

[mumps]

The code assumes that all cells in column A in the "Ordered" sheet will always have data. Is this the case or is it possible for some cells in column A to be blank when you select "Yes" in column M?

 

[amburnham23]

The code assumes that all cells in column A in the "Ordered" sheet will always have data. Is this the case or is it possible for some cells in column A to be blank when you select "Yes" in column M?

That depends, if there is a way to auto generate the date then there should always be something in column A. If not, is there an easy way to make it check column B?

 

[mumps]

This version of the code will place the date and time in column A when you select "Yes" in column M and then it will copy the row to the "Invoiced" sheet and delete it from the "Ordered" sheet. Will this work for you?

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("M:M")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target = "Yes" Then
        With Cells(Target.Row, 1)
            .Value = Now
            .NumberFormat = "mmm d,yyyy h:mm AM/PM"
        End With
        Target.EntireRow.Copy Sheets("Invoiced").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        Target.EntireRow.Delete
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub