Link to home
Create AccountLog in
Avatar of Theva
ThevaFlag for Malaysia

asked on

Accumulate data at cell

Hi Experts,

I would like to request Experts help to add additional feature in the attached script. Need to copy and accumulate date value from Column_C at Send sheet to Status sheet at Column B and Column_C at Return sheet to Status sheet at Column C and update column D with the latest location sheet of the number. I have attached the sample data at Status sheet for Experts to get better view. Hope Experts will help me to add this feature.



Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        Target.Offset(1, 0).Select
    End If
    If Target.Row < 3 Then Exit Sub
    If Not Application.Intersect(Target, Columns(1)) Is Nothing Then
        Target.Offset(, 1) = Environ("username")
        Target.Offset(, 2) = Now()
      
        With Worksheets("Status")
            Set r = .Columns(1).Find(Target.Offset(, 0), LookIn:=xlValues)
            If r Is Nothing Then
                rn = .UsedRange.Rows.Count + 1
                .Cells(rn - 1, 1).Copy .Cells(rn, 1)
                .Cells(rn, 1).Value = Target.Offset(, 0)
            End If
        End With
    End If
End Sub

Open in new window

DataCopy.xls
ASKER CERTIFIED SOLUTION
Avatar of Peter Kwan
Peter Kwan
Flag of Hong Kong image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Theva

ASKER

Hi,

Thanks for the code. I noticed at Status sheet when we update the C-number its always copying the row 2 cell format (color and font type) how to prevent this?  
Just remove the two lines:

.Cells(rn - 1, 1).Copy .Cells(rn, 1)
.Cells(rn - 1, 3).Copy .Cells(rn, 3)
Avatar of Theva

ASKER

Hi,

Thanks a lot for the superb solution
Avatar of Theva

ASKER

Hi,

Hope you will consider this request. Intent to integrate new feature  with your solution.

https://www.experts-exchange.com/questions/26623691/Change-word-from-Pending-to-Send.html