Excel: Combine all sheets into one

From https://professor-excel.com/merge-sheets/

Modified to only copy in values, not formulas, and also add formatting.

Sub Merge_Sheets()

' From https://professor-excel.com/merge-sheets/

' Modified to only copy in values, not formulas, and also add formatting.

' Select the sheets to merge first.

' Edward Chan 2023

    Dim selectedSheets As Sheets

    Set selectedSheets = ActiveWindow.selectedSheets

    'Insert a new worksheet

    Sheets(1).Select

    Sheets.Add

    'Rename the new worksheet

    ActiveSheet.Name = "ProfEx_Merged_Sheet"

    'Loop through worksheets and copy the to your new worksheet

    For Each ws In selectedSheets

        ws.Activate

        'Don't copy the merged sheet again

        If ws.Name <> "ProfEx_Merged_Sheet" Then

            ws.UsedRange.Select

            Selection.Copy

            Sheets("ProfEx_Merged_Sheet").Activate

            'Select the last filled cell

            ActiveSheet.Range("A1048576").Select

            Selection.End(xlUp).Select

            'For the first worksheet you don't need to go down one cell

            If ActiveCell.Address <> "$A$1" Then

                ActiveCell.Offset(1, 0).Select

            End If

            'Instead of just paste, you can also paste as link, as values etc.

            Selection.PasteSpecial Paste:=xlPasteValues

            Selection.PasteSpecial Paste:=xlPasteFormats

        End If

    Next

End Sub

Also, you can try Rob de Bruin's RDBMerge Excel Addin (https://web.archive.org/web/20211111101327/https://www.rondebruin.nl/win/addins/rdbmerge.htm)