Need to copy multiple worksheets and compile date into a master sheet

I'm stuck! I have a workbook that has 7 sheets. "All Sites", which is where I want all my data compiled, and 5 other sheets named, Brookfield, Sudbury, Elkhart, Mishawaka, Walpole, and Site Not Assigned. They are in that order. Each sheet has row 1 with headers in columns A through AJ. All sheets are the same except that the number of rows is different. I only want the rows with data entered to be copied over and appended to the master sheet. I want the master sheet to update automatically any time data is entered into any of the other sheets. Can someone help me?

None of those seem to work for me.

The below seems to work for me. I'm wondering if there is a way to make this so it will automatically update whenever new data is entered into any of the non "mergedata" sheets. Like if the "mergedata" sheet could exist all the time and auto-update. Any ideas?

Sub MergeData()
Dim ws As Worksheet, ws1 As Worksheet
Dim lr As Long, lrm As Long, lc As Integer
Worksheets.Add(After:=Worksheets(Sheets.Count)).Name = "Merged Data"
Set ws1 = Sheets("Merged Data")
For Each ws In Sheets
If ws.Name <> "Merged Data" Then
ws.Activate
lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
lc = ws.Cells(1, Columns.Count).End(xlToLeft).Column
If ws1.Range("A1") = "" Then
Range(Cells(1, 1), Cells(1, lc)).Copy Destination:=ws1.Range("A1")
End If
lrm = ws1.Cells(Rows.Count, 1).End(xlUp).Row + 1
n = lrm
ws.Activate
Range(Cells(2, 1), Cells(lr - 1, lc)).Copy Destination:=ws1.Range("A" & lrm)
ws1.Activate
lrm = ws1.Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(n, lc + 1), Cells(lrm, lc + 1)).Value = ws.Name
End If
Next ws
MsgBox "Data Is Merged Successfully"
End Sub

Yes. It works but not exactly as I want it to. I lose the formatting that the source sheets have in the newly created Master sheet.

Cardis01

You need to use code tags with your code as per forum's rule.

Please Login or Register  to view this content.