Auto populate code - needs tweaking slightly

[riskreep]

I have multiple worksheets that will be used to input data, and will have a Master Sheet which will automatically populate itself, consolidating the data from the multiple worksheets into the one master sheet.

I have the code for this and it works, however the master sheet is updated in the sequential order that the worksheets are in.

For example, I have MASTER SHEET, then SHEET ONE, SHEET TWO, SHEET THREE. When I update SHEET ONE and SHEET THREE they follow in order. But if I then update SHEET TWO it will move above SHEET THREE. I would like to make a subtle tweak to the code to just keep the order the master is updated based on time sequence, i.e when the worksheets are updated, and not the worksheet sequence.

Does that make sense?


Here is the code:

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