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?
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 Sheets.Add ActiveSheet.Name = sConsolidatedSheet For Each Sheet In Sheets If Sheet.Name = sConsolidatedSheet Then GoTo Next_Sheet If sLastCol = "" Then sLastCol = Sheet.Cells(1, Columns.Count).End(xlToLeft).Address 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.