Merging excel workbooks

[mel_flynn]

I have a number of workbooks. Some may only contain 1 worksheet, some more. I want to programmatically merge all worksheets into the one, new, workbook.

I can sucessfully do this, when I know how many worksheets in each workbook and each worksheet name. However, I would prefer not to hardcode in the worksheet names. Can I write some code that wil figure out how many worksheets are contained in each workbook, and the worksheet name. Then loop through to merge all worksheets into the one workbooks?

[RobDog888]

Use the Sheets collection to get the number of sheets in the workbook. Then to refer to the workbook or sheet without hard coding the names, use their index numbers instead.

[mel_flynn]

But I want to retain the sheet's name in the destination workbook.

[Webtest]

This will copy all of the sheets, including all formatting, headers, page setup, etc. from a source workbook into a destination workbook:

    'This will append all sheets from the "bBook" after the last sheet of the "aBook"
    Call Macro1(bBook, aBook)

Sub Macro1(ByRef wbkSrc As Workbook, ByRef wbkDst As Workbook)
    Dim aSheet As Worksheet
    
    For Each aSheet In wbkSrc.Sheets
        aSheet.Copy after:=wbkDst.Sheets(wbkDst.Sheets.Count)
    Next aSheet
    
End Sub