Macro to merge worksheets

[MikeCM]

Within a single workbook, I have 12 worksheets called, say,
"Worksheet A", "Worksheet B",..... through to "Worksheet
L", (being departments for example) which contain common data tables
and alike data. In each worksheet, row 1 is a standard set of column
headers and this is common across all 12 worksheets. A worksheet may be
completely blank/empty, however, should there be no data for that
particular department.

I need to merge all of these worksheets into a single worksheet within
the same workbook called, say, "Consolidated worksheet" which has
the single header row and all of the data in a single combined table.
Is there a macro that will do this automatically for me?

Thanks for help/suggestions.

 

[vidguru]

Sorry, this isn't exactly what you want, but it is a step in the right
direction.

Sub CombineSheets()
Dim PasteRange As Range
Dim i As Long
Application.ScreenUpdating = False
Set PasteRange = Worksheets(1).Range("a65536") _
.End(xlUp).Offset(1, 0)
For i = 2 To Worksheets.Count
Worksheets(i).UsedRange.Copy PasteRange
Set PasteRange = Worksheets(1).Range("a65536") _
.End(xlUp).Offset(1, 0)
Next i
End Sub



This code will combine all pages into one page. I would create the tab
you want the data to be on, and run this macro from that tab. Problem
is, this will copy everything, including header rows. You can then
sort once the tabs are combined, and then delete the duplicate header
rows. Hope this helps!