merging excel worksheets

[engnouna]

Hi
I have over 20 excel Worksheets in different workbooks that have the same headers.I want to merge them into one single worksheet in a specific workbook.
How can I do it?
Thanks in advance

[Ecniv]

carefully.
Possibly with code.
Or import into an mdb, and put them into the same table?

If coding, you need to list/select all files you want to merge, then the code would copy and paste the range of data from each sheet into the combining sheet. Must be less than 65500 rows tho.

[Webtest]

I have over 20 excel Worksheets in different workbooks that have the same headers.I want to merge them into one single worksheet in a specific workbook.

Is that 20 Sheets in 20 Books? ... or are there some Books with more than one Sheet? Are all of the books open when you start the macro, or do you need to open them? Might there be any other books open at the same time?

[engnouna]

Is that 20 Sheets in 20 Books? ... or are there some Books with more than one Sheet? Are all of the books open when you start the macro, or do you need to open them? Might there be any other books open at the same time?

There are 20 sheets in 20 books,every book contain one sheet,I want to merge them into one book containing one sheet which is the sum of the 20 sheets,there is no other book open at the same time

[RobDog888]

To do it from VB 6...

Option Explicit
'Add a reference to MS Excel xx.0 Object library
Private Sub Command1_Click()
    Dim oApp As Excel.Application
    Dim oWBMain As Excel.Workbook
    Dim oWB As Excel.Workbook
    Dim oSht As Excel.Worksheet
    Dim i As Integer
    Set oApp = New Excel.Application
    'Create a new blank workbook to hold all the sheets
    Set oWBMain = oApp.workbboks.Add
    'Open Book1.xls - Book20.xls
    For i = 1 To 20
        'Open a workbook
        Set oWB = oApp.Workbooks.Open("C:\Book" & i & ".xls")
        'Copy and paste the sheet in our main workbook at the end of the sheet tabs
        oWB.Sheets(1).Copy After:=oWBMain.Sheets(oWBMain.Sheets.Count)
        'Close and clean up
        oWB.Close SaveChanges:=False
        Set oWB = Nothing
    Next
    
End Sub

[Webtest]

There are 20 sheets in 20 books,every book contain one sheet,I want to merge them into one book containing one sheet

Are the WorkBOOK names predictable ... like book01, book02, etc. ... or do they have independent names? If they have predictable names you can construct the file name in a loop. Otherwise, you'll have to create an array with all of the files names. I wouldn't guess that you would want all 20 of the files open at the same time. It is too easy to open them one at a time, pull the info off of the sheet for merging, and close the file. Answer this one and I'm sure you'll get some code on the next iteration.

[engnouna]

Hi webtest
The names of the books are differents like indexDaily,indexmorning,mondaymorning...
I just want to merge those files,so I don't care about opening them at once or no,
Thanks in advance

[RobDog888]

If you place all the workbooks in a particular folder by themselves then you can enumerate the files in VBA and open them in a loop and use with my code.

[Webtest]

engnouna ...

I thought I sent a message yesterday, but I guess something got fouled up. What I wanted to say was that you need to give us a good definition of the folder(s) where your 20 source files and your target file are found. Without that, it is nearly impossible to write the code to find and open the source sheets.

Here is a function to append any Source sheet (excluding header rows, if any) to any Destination sheet, and a simple TEST subroutine. Make sure that you look at the "ActualUsedRange" function posted in this forum:Function "Actual Used Range" Posting

Sub TEST_APPEND()    'TEST the Append Call
    Dim rowLast As Long        'Last Row on the DESTINATION Sheet
    Dim NumHeaderRows As Long  'Number of Header Rows on ALL Source Sheets
    Dim arange As Range        'Miscellaneous Range Variable
    Dim aBool As Boolean       'Return Parameter for the Append call
    
    'Initialize the Number of Header Rows that are on ALL of the Source Sheets
    NumHeaderRows = 2
    'Find the Last Row on the Destination Sheet ... Source sheets will be appended AFTER this row.
    rowLast = ActualUsedRange(Sheets("sheet1")).Rows.Count
    'Do the APPEND Call
    aBool = AppendSrcToDest(Sheets("sheet3"), Sheets("Sheet1"), rowLast, NumHeaderRows)
    'Show the Results of the APPEND Call
    If aBool Then
        MsgBox "The APPEND completed successfully. Now Last Row = " & rowLast
    Else
        MsgBox "ERROR:  The APPEND FAILED!"
    End If
    
End Sub
'
'=================================================================================================
'Function to Append entire Source sheet (less Header Rows) to Destination sheet
'By:  Arthur Du Rea    060603.0912
'
'Parameters;
'  shtSrc   Sheet to copy to destination sheet
'  shtdst   Sheet on which to append copy of Source Sheet
'  rowDst   Number (Long) of the current Last Used Row in the Destination Sheet
'  numHead  Number of Header Rows on the Source sheet that will NOT be copied
'Returns:   TRUE on success;  FALSE on failure
'  Updates Caller "Destination Row" to new "Last Row in Destination" due to added rows
'
'As written requires function "ActualUsedRange(aSheet)".  "UsedRange" might work equally well.
'
Function AppendSrcToDest _
       (ByRef shtSrc As Worksheet, ByRef shtDst As Worksheet, ByRef rowDst As Long, numHead As Long) _
        As Boolean
    Dim aRange As Range  'Miscellaneous Range Variable
    Dim aCell As Range   'Range of Last Cell in Source Sheet Actual Used Range
    
    'Set up the Error Return in case of errors
    On Error GoTo ERROR_RETURN
    'Find the range used in the Source Sheet
    Set aRange = ActualUsedRange(shtSrc)
    'If the Source Sheet is BLANK, don't do anything.  NOTE: UsedRange returns "A1" for Empty Sheet!
    If aRange Is Nothing Then AppendSrcToDest = True: Exit Function
    'If the Source Sheet only contains HEADER Rows don't do anything
    If aRange.Rows.Count <= numHead Then AppendSrcToDest = True: Exit Function
    'Find the Last Cell on the Source Sheet
    Set aCell = aRange.Cells.SpecialCells(xlCellTypeLastCell)
    'Set the Range on the Source Sheet to append to exclude the header rows
    Set aRange = shtSrc.Range(shtSrc.Cells(numHead + 1, "A"), aCell)
    'Append the actual Data Area of the Source Sheet to the Destination Sheet
    aRange.Copy Destination:=shtDst.Cells(rowDst + 1, "A")
    'Set the Return status to "PASSED"
    AppendSrcToDest = True
    'Update the Caller's Row Counter
    rowDst = rowDst + arange.Rows.Count
    'Clean House on Exit
    GoTo ALL_RETURNS

ERROR_RETURN:
    'Set the return parameter to "FAILED"
    AppendSrcToDest = False
ALL_RETURNS:
    'Clean House
    Set arange = Nothing
    Set aCell = Nothing
    On Error GoTo 0
    
End Function

If you tell us how and where to find your source and destination sheets, we will tell you how to iterate through them and use the above function to append your 20 source sheets to your target/destination sheet. In the mean time, you can modify the TEST_APPEND subroutine to test your own files/sheets.

Good Luck and Good Programming!