Copying data from one cell to another in Excel

[csharpit]

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?

[dglienna]

If you can do it in a spreadsheet, you can RECORD A MACRO and then LOOK at the code. Nobody writes macros....

[Eri523]

The problem with writing the change log to the same sheet probably is that writing to the change log cells re-triggers the worksheet change event, tossing your code into an infinite recursion. Try wrapping the entire body of your Worksheet_Change() handler into an If condition that skips it when it detects the write goes to the change log. (Alternatively, do an Exit Sub right at the start of the handler upon detection of a log write.)

Nobody writes macros....

I do.