move certain data when certain column is updated

[steve400243]

Hello, on the attached worksheet when Column L, in the 2017 LOG tab is updated to “EMPTY” If there is the Letter “D” in any row, column D, I need the information from that row in column’s C, A, I, to copy to the Distribution Billing tab to columns A, B, C. Column D on the Distribution Billing Tab would populate the date and time that the data copied over. Make sense? Please let me know if this can be done. Thank you very much

Also posted at

http://www.vbaexpress.com/forum/sho...e-certain-data-when-certain-column-is-updated

 

[vletm]

Many things can be done ...
Did You mean something like this?
... do "EMPTY" after "D" has selected (as You wrote!)

 

[steve400243]

Thank you much vletm, this is what was needed and will work great. Appreciate your time and code! Have a nice Day.

 

[steve400243]

One question, is there a way to have it copy only when a user changes the status to "Empty". It seems to copy on all status changes if the letter is present. Thank you

 

[vletm]

Oh Yeah!
Is it normal procedure to mark it
'COMPLETE' -> 'EMPTY' -> 'COMPLETE' -> 'EMPTY' ...
but I can show one possible to get ONLY one row from the 1st change!
If marks to "AA" timestamp.
... of course 'changes' after that are still possible to do in this version too.

 

[steve400243]

Hi, Yes it is normal for the user to change the status Column randomly. Only when it changes to the " EMPTY" status, will it need to copy to the Distribution tab. The Column D will be updated, with the letter D before the status changes to Empty. It will change to empty only once. Make sense?

 

[steve400243]

Hi Vletm, I was able to get it working as needed after a couple of searches on line, here is the working code - Thanks for your help.

Private Sub Worksheet_Change(ByVal Target As Range)
    AY = Target.Row
    ax = Target.Column
    If ax <> 12 Then Exit Sub
    With ActiveSheet
          If UCase(.Cells(AY, 4).Value) = "D" And UCase(Target.Value) = "EMPTY" Then
            Application.ScreenUpdating = False
            d_tab = "Distribution Billing"
            y = Sheets(d_tab).Cells(.Rows.Count, 1).End(xlUp).Row + 1
            Sheets(d_tab).Cells(y, 1) = .Cells(AY, 3)
            Sheets(d_tab).Cells(y, 2) = .Cells(AY, 1)
            Sheets(d_tab).Cells(y, 3) = .Cells(AY, 9)
            Sheets(d_tab).Cells(y, 4) = Now
            Sheets(d_tab).Cells(y, 4).NumberFormat = "dddd, mmm/dd/yyyy hh:mm"
            Application.ScreenUpdating = True
        End If
    End With
End Sub

 

[vletm]

@steve400243
Sometimes it's not good time (1AM) to make modifications ... sometimes...
You modifications works ... but some features ... not
Test this 'morning'-version.
"Empty Once!" - "Empty Always!"