Copying data from multiple worksheets

Hello,

I'm trying to copy data from multiple worksheets to one central worksheet using a macro. The data from each worksheet - which is constantly being added to - would have to go in the empty cell on the main worksheet. The main worksheet is a compilation of all the data.

Also, it must be possible that any data previously added would be updated should changes be made.

Here's what I've put together from previous forum answers:

Sub CalendarProducers()
Dim X As Long
Dim LastRow As Long
Dim Source As Worksheet
Dim Destination As Worksheet
Dim RowsWithText As Range
Set Source = Worksheets("Producers")
Set Destination = Worksheets("Calendar")
With Source
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For X = 3 To LastRow
If IsNumeric(.Cells(X, "C").Value) And .Cells(X, "C").Value <> "" Then
MsgBox "Data contains incorrect data type.", vbExclamation + vbInformation, "Error"
Else
If RowsWithText Is Nothing Then
Set RowsWithText = .Cells(X, "C")
Else
Set RowsWithText = Union(RowsWithText, .Cells(X, "C"))
End If
End If
Next
If Not RowsWithText Is Nothing Then
RowsWithText.EntireRow.Copy Destination.Range("A3")
End If
End With
MsgBox "Calendar sheet data has been updated.", vbExclamation + vbInformation, "Data Updated"
End Sub


Specifics:
1. Data starts at "A3" and goes to row "C".
2. Must get data from all 8 other worksheets and add them into empty cells in the main "Calendar" worksheet.
3. Would be ideal if they then sorted themselves in the "Calendar" worksheet by date (which is in column "A")
4. Would be ideal if it ran automatically when the document opened, or if there was a button that users could push to have it self-update.

The other option I've thought about would be having users update the main "Calendar" worksheet and have the data separated into their respective worksheets by their "Lane" identifiers (given in column "B" and is same as worksheet names).

Would appreciate ANY help!

MIMI