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 ...

Do

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

Next



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?