Excel 2007 : Create an archive or log of data on separate sheet

Hi there!

I'm a bit of a novice when it comes to these things, but I was hoping someone could help me! I have looked through the forum but couldn't find anything (so apologies if there already is an answer out there), but what I am trying to do is to create a 'log' or an archive of all data that is entered into one sheet on another sheet.

Basically, the idea is that an initial entry is created on one sheet, however if revisions are made to that entry, then an archive is able to log both entries so the change can be recorded. At the moment I can get the data being copied across and the cursor to move down, yet when I make changes to the entry, it simply mirrors the changes on the archive sheet, rather than creating a new entry.

I'm hoping this is an easy thing to fix!

Thanking you all in advance!

Arneld

The code I am using is here:

Private Sub Worksheet_Activate()
Dim Sheet As Worksheet
For Each Sheet In Me.Parent.Sheets
If Sheet.Name <> Me.Name Then
If Sheet.Cells(Rows.Count, 1).End(xlUp).Row <> 1 Then
Sheet.Range(Sheet.Cells(2, 1), Sheet.Cells(Sheet.Cells(Rows.Count, 1).End(xlUp).Row, 10)).Copy Destination:=Me.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

End If
Else
Me.Range(Cells(2, 1), Cells(Rows.Count, 10)).Clear
End If
Next Sheet
End Sub