PDA

View Full Version : [SOLVED] Need Way to move certain data when certain column is updated



steve400243
03-16-2017, 10:39 AM
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 2017 log Tab was updated to Empty, and the data copied over. Make sense? Please let me know if this can be done. Thank you very much.

steve400243
03-16-2017, 02:28 PM
Hello, If anyone is interested I got this to work using the following code -


Private Sub Worksheet_Change(ByVal Target As Range)
ay = Target.Row
ax = Target.Column
If ax <> 12 Then Exit Sub
With ActiveSheet
If .Cells(ay, 4) = "D" 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

steve400243
03-16-2017, 04:53 PM
had a small change to make and now it is working as needed.

From

If .Cells(ay, 4) = "D" Then

to

If UCase(.Cells(AY, 4).Value) = "D" And UCase(Target.Value) = "EMPTY" Then

mdmackillop
03-17-2017, 03:15 AM
Hi Steve
FYI if you declare "Option Compare Text" at the head of your code, you can avoid Case issues.

steve400243
03-17-2017, 06:03 AM
Hi Steve
FYI if you declare "Option Compare Text" at the head of your code, you can avoid Case issues.

Thankyou for the option tip MD. I appreciate it.