Excel VBA Combine all sheets into workbook into one sheet

Hi Abdullah MSL

Try this macro, I tested on my side and works OK

The macro will do the following

1- Create a new sheet and name it "Master"

2- Loop thru all the sheets in the workbook, transfer their data to the master sheet by creating a new page brake for each of them on Master sheet

3- Delete the transferred sheet

***************************************************************************

Sub CombineSheetswithPageBrakes()

Dim MasterSh As Worksheet

Dim QSh As Worksheet

Dim break As String

Application.ScreenUpdating = False

Application.DisplayAlerts = False

Set MasterSh = ThisWorkbook.Sheets.Add

MasterSh.Name = "Master"

For Each QSh In ThisWorkbook.Sheets

    If QSh.Name <> "Master" Then

            With MasterSh

            ''' Find the Page Break address

            break = .UsedRange.Offset(MasterSh.UsedRange.Rows.Count + 2).Resize(1, 1).Address

            '' Inserting the new page break

            .HPageBreaks.Add Before:=.Range(break)

           End With

           '' Copy/Paste (Transfer) the values form the other sheets to Master sheet

            QSh.UsedRange.Copy MasterSh.UsedRange.Offset(MasterSh.UsedRange.Rows.Count + 2).Resize(1, 1)

            '' Delete the sheet after transfer it to master sheet

            QSh.Delete

    End If

Next QSh

MasterSh.Rows("1:3").Delete

Application.ScreenUpdating = True

Application.DisplayAlerts = True

End Sub

******************************************************************************

Do let me know if you need more help

Regards

Jeovany