Copying data from multiple worksheets

Mimi - Jul 6, 2010 at 12:32 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jul 6, 2010 at 06:13 PM

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"
If RowsWithText Is Nothing Then
Set RowsWithText = .Cells(X, "C")
Set RowsWithText = Union(RowsWithText, .Cells(X, "C"))
End If
End If
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

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!


1 response

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jul 6, 2010 at 06:13 PM
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like ,, , etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too