Consolidating Worksheets Into One

[mchel_27]

Hello,

I have a workbook with over 300 worksheets in it. I want to combine all of them into one worksheet so that it becomes workable data for me. All of the worksheets have columns that have the same type of data in them and they are formatted the same. Is there an easy way to do this?

Thanks!

 

[VoG]

This should help you http://msdn.microsoft.com/en-us/library/cc793964.aspx

 

[mchel_27]

Thanks! Worked like a charm!

 

[gobishg]

See the Attached link, this also works Better.

Consolidating Multiple Sheets in Excel

 

[MrKowz]

Another possibility:

Public Sub ConsolidateWorkbook()
'Variable Declaration: dWS = Destination Worksheet, sLR = Source Worksheet Last Row
'                      dLR = Destination Last Row
Dim ws          As Worksheet, _
    dWS         As Worksheet, _
    sLR         As Long, _
    dLR         As Long, _
    RowHeader   As VbMsgBoxResult
    
Application.ScreenUpdating = False
Sheets.Add Before:=Sheets(1)
ActiveSheet.Name = "Master"
Set dWS = ActiveSheet
RowHeader = MsgBox("Do your worksheets contain a header?", vbYesNo)
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> dWS.Name Then
        dLR = dWS.Range("A" & rows.Count).End(xlUp).Row + 1
        sLR = ws.Range("A" & rows.Count).End(xlUp).Row
        Select Case RowHeader
            Case vbYes
                ws.Range("A2:A" & sLR).EntireRow.Copy Destination:=dWS.Range("A" & dLR)
            Case vbNo
                ws.Range("A1:A" & sLR).EntireRow.Copy Destination:=dWS.Range("A" & dLR)
        End Select
    Else
        If RowHeader = vbYes Then Sheets(Sheets.Count).rows(1).Copy Destination:=dWS.Range("A1")
    End If
Next ws
Application.ScreenUpdating = True
End Sub