July 27th, 2013, 10:25 PM
Copying data from one cell to another in Excel
I am trying to write a Macro in Excel which makes a copy of some of the information entered and a timestamp of when the change was made. It also creates a new entry if existing information is edited and it gives the timestamp.
So far I have the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AuditRecord As Range
' This is our change history
Set AuditRecord = Worksheets("ChangeHistory").Range("A1:B1")
r = 0
'Now find the end of the Change History to start appending to ...
r = r + 1
Loop Until IsEmpty(AuditRecord.Cells(r, 2))
'For each cell modified ...
For Each c In Target
Value = c.Value
Row = c.Row
' ...update Change History with Value and time stamp of modification
AuditRecord.Cells(r, 1) = Worksheets("Products").Cells(Row, 1)
AuditRecord.Cells(r, 2) = Worksheets("Products").Cells(Row, 2)
AuditRecord.Cells(r, 3).NumberFormat = "hh:mm:ss"
AuditRecord.Cells(r, 3).Value = Now
This copies the data from the "Products" sheet into the "ChangeHistory" sheet and applies the timestamp. However I need the data copied into the same sheet (Products) ,and starting from row 200.
I have tried many different things but cannot get this to work on the same sheet.
Is this even achievable?
Tags for this Thread
Click Here to Expand Forum to Full Width
This is a CodeGuru survey question.