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
ThevaAsked:
Who is Participating?
 
Peter KwanConnect With a Mentor Analyst ProgrammerCommented:
Please try. But one point to note, if you add two rows of the same CNumber in the same day (e.g. 16-Nov), then there will be "16-Nov;16-Nov" in the same cell.
DataCopy.xls
0
 
ThevaAuthor Commented:
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?  
0
 
Peter KwanAnalyst ProgrammerCommented:
Just remove the two lines:

.Cells(rn - 1, 1).Copy .Cells(rn, 1)
.Cells(rn - 1, 3).Copy .Cells(rn, 3)
0
 
ThevaAuthor Commented:
Hi,

Thanks a lot for the superb solution
0
 
ThevaAuthor Commented:
Hi,

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

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26623691.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.