macro Copying worksheets from multiple open workbooks into current workbook.

[RAM1972]

I have 4 to 6 workbooks opened in which there are sheets , the sheet names have no duplicates names across the workbook opened .

I looking a macro that copy all sheets in the opened workbook to current book .

The current workbook has only one sheet named master1.

I got some similarities but tested bit not worked

https://excel.tips.net/T007425_Combining_Worksheets_from_Many_Workbooks.html

 

[mumps]

What is the name of the workbook containing the "Master1" worksheet?

 

[RAM1972]

The name of the workbook is master control in which there is one sheet tab Master1

 

[mumps]

Do you want to copy all the sheets from the source workbooks to the "Master1" sheet so they are all on one sheet or do you want to copy the sheets as separate sheets to the Master Control workbook?

 

[RAM1972]

To copy as separate sheets to the master control workbook because the ranges headers varies per sheet

 

[mumps]

Place this macro in a regular module in the current workbook and run it from there.

Sub CopySheets()
    Application.ScreenUpdating = False
    Dim WB As Workbook
    Dim ws As Worksheet
    Dim desWB As Workbook
    Set desWB = ThisWorkbook
    For Each WB In Workbooks
        If WB.Name <> desWB.Name Then
            For Each ws In WB.Sheets
                ws.Copy desWB.Sheets(desWB.Sheets.Count)
            Next ws
        End If
    Next WB
    Application.ScreenUpdating = True
End Sub

 

[RAM1972]

Thank you Mumps worked perfectly

 

[mumps]

You are very welcome.