-
July 27th, 2013, 09:25 PM
#1
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 ...
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?
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|