I have several excel workbooks (.xls files) and each workbook contains multiple worksheets. The number of worksheets (as well as their names) are not constant. Each worksheet is formatted in the same way (i.e: same header). Now I want to copy all the data on each worksheet and copy it into a single worksheet.

Launch an Excel file that you want to combine other workbooks into. Click Developer > Visual Basic, a new Microsoft Visual Basic for applications window will be displayed, click Insert > Module, and input the following code into the Module.

Oh, and before that, create a sheet called "Summary" where we will collect the info.

Sub SummarizeSheets()
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    Sheets("Summary").Activate
    Path = "C:\path\to\your\files\"
    Filename = Dir(Path & "*.xls")
    Do While Filename <> ""
        Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
            For Each Sheet In ActiveWorkbook.Sheets
                For Each ws In Worksheets
                    If ws.Name <> "Summary" Then
                        ws.Range("A01:AX201").Copy
                        ActiveSheet.Paste Range("A65536").End(xlUp).Offset(1, 0)
                    End If
                Next ws
            Next Sheet
        Workbooks(Filename).Close
        Filename = Dir()
    Loop
End Sub

Then click doc-merge-multiple-workbooks-button button to run the code, and all of the worksheets (including the blank worksheets) within the workbooks have been merged into the master workbook.

References: http://www.extendoffice.com/documents/excel/456-combine-multiple-workbooks.html