0
Thanks

A few words of thanks would be greatly appreciated.

# Excel - Merge several worksheets into a single one

## Issue

I use Excel to store information about several products and have a different worksheet for each range. How to merge (sequentially combine) all these worksheets into single page? All worksheets the same number of columns, with the same title (in the top row).
How to proceed?

## Solution

Assuming that:
1. A sheet titled Consolidated can be created and deleted by macro
2. Headers are on row 1 on all sheets and is common for all
3. All sheets are to be copied

```Sub CombineSheets()
Dim lConRow As Long
Dim Sheet As Variant
Dim sConsolidatedSheet As String
Dim lSheetRow As Long
Dim sLastCol As String

sConsolidatedSheet = "Consolidated"

On Error Resume Next

Sheets(sConsolidatedSheet).Delete

On Error GoTo 0

ActiveSheet.Name = sConsolidatedSheet

For Each Sheet In Sheets

If Sheet.Name = sConsolidatedSheet Then GoTo Next_Sheet

If sLastCol = "" Then
Sheets(sConsolidatedSheet).Range("1:1") = Sheet.Range("1:1").Value
lConRow = 1
End If

lSheetRow = 0

On Error Resume Next
lSheetRow = Sheet.Cells.Find("*", Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
On Error GoTo 0

If (lSheetRow > 1) Then
Sheets(sConsolidatedSheet).Range(lConRow + 1 & ":" & lSheetRow + lConRow - 1) = Sheet.Range("2:" & lSheetRow).Value
lConRow = Sheets(sConsolidatedSheet).Cells.Find("*", Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

End If

Next_Sheet:

Next
End Sub```

Thanks to rizvisa1 for this tip.

0
Thanks

A few words of thanks would be greatly appreciated.