-1

I have several, multi-sheet workbooks which I would like to combine so that I can search them all simultaneously. So, the merged workbook will contain tabs 1,2,3, of the first workbook, then tabs 1,2,3 of the next workbook.

I know nothing about programming VBA or macros or anything like that. I would be really grateful if someone could give me a script and tell me exactly how to modify it.

Thanks for reading!

1
  • 1
    How many is several? For about 10 or so, it's easy enough to do manually, just select all sheets and right-click, move or copy to new workbook.
    – mcalex
    Feb 27, 2014 at 8:46

1 Answer 1

0

Download the RDBMerge Add-in. This would be the best way to accomplish it.

Download and put it somewhere you can find it and enable it in excel. I recommend using the default add-in location at C:\Documents and Settings\User\Application Data\Microsoft\AddIns\

Click on File, click on Options, click the Add-ins tab. In the Manage drop-down, choose Excel Add-ins, and click Go. Use “Browse” to go to the add-in and then click on OK. Verify RDBMerge is checked in the add-in list and then click OK.

Then in excel you'll find a button on the ribbon for it, click it and follow the directions.


Or with VBA

Sub CopyBooks()
    Application.ScreenUpdating = False
    Application.Calculation = xlManual

    Dim destinationWorkbook As Workbook
    Set destinationWorkbook = ThisWorkbook
    Dim sourceWorkbook As Workbook
    Dim sourceWorksheet As Worksheet
    Const path As String = "C:\path\to\"
    Dim file As Variant

    Dim currentSheets As Long
    currentSheets = destinationWorkbook.Sheets.Count

    file = Dir(path & "*.xl*")

    While file <> ""
        Set sourceWorkbook = Workbooks.Open(path & file)
            For Each sourceWorksheet In sourceWorkbook.Worksheets
                sourceWorksheet.Copy after:=destinationWorkbook.Worksheets(currentSheets)
                currentSheets = currentSheets + 1
            Next
            sourceWorkbook.Close savechanges:=False
            file = Dir
    Wend

    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True
    End Sub

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .