Combining multiple worksheets (tabs) into one tab/worksheet

**COGICPENNY** · 09-25-2012, 12:35 AM

Hi,

I have a worksheet that has approximately 114 tabs of which I want to create ONE master worksheet consisting of only 1 tab.

I have no clue how to program "code" to do this, and personally just want a quick fix to doing this because I don't know what I'm doing.

**JieJenn** · 09-25-2012, 12:41 AM

Buy me a dinner next time when you see me.

Sub CombineSheets()
Dim ws As Worksheet, wsMaster As Worksheet

Set wsMaster = Worksheets("Master")
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Master" Then
        ws.Cells(1, 1).CurrentRegion.Copy _
        wsMaster.Cells(wsMaster.Cells(Rows.Count, 1).End(xlUp).Row, 1)
    End If
Next ws
End Sub

**bheanloh** · 09-25-2012, 01:05 AM

A rather lengthy one. Will delete all your sheets.

Sub test()
Dim mws As Worksheet
Dim ws As Worksheet
Dim lrow As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set mws = Sheets("Table 1")
lrow = mws.Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> mws.Name Then
        Range(ws.Cells(2, 1), ws.Cells(Rows.Count, 1).End(xlUp). _
        Offset(, 7)).Copy mws.Cells(lrow, 1)
        lrow = mws.Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
        ws.Delete
    End If
Next
mws.Cells.RowHeight = 11
mws.Name = "Master Table"
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub