Downloads

Download Example workbook with all the code
Open a txt file with all code if you are not allowed to download a zip file


Information

Use Alt F11 to open the VBA editor and you see that there are four modules in this workbook.
The code in the module named "Basic_Code_Module" will be used by every example in this workbook.
Note: you not have to change anything in this module.

You see that there are three other modules with in every module a example.
There are two macro's in every module but you only run the macro's you see on this page.

RDB_Merge_Data in the "Get_Data_Macro" module
To merge data from all workbooks in a folder
Read the information above the macro
Note: There is also a macro example "RDB_Merge_Data_Browse" that let you browse to the folder

RDB_Copy_Sheet in the "Get_Sheet_Macro" module
To copy a sheet from all workbooks in a folder
Read the information above the macro

RDB_Filter_Data in the "Get_Filter_Macro" module
To merge the filter results from a sheet from all workbooks in a folder
Read the information above the macro

Make you changes and run the macro in the VBA editor or with Alt F8 in Excel.
It will add a new workbook with the data for you and it is up to you if you want to save it.
Note: the workbook with the code must be outside the merge folder

Tip: It is also possible to use a cell reference in the code for the arguments.



Copy a range from each workbook

Note: This is only the macro that you must change and not all the code that you need.
Download the example workbook to test the code.

The example macro below you can use to merge a fixed range or
all cells from one or all worksheets from each workbook in a folder

The example below will merge A1:G1 from the first worksheet from each Excel file in the
folder "C:\Users\Ron\test" to a new workbook.
It will use a fixed range on the first worksheet because SourceShName and StartCell are ""

Sub RDB_Merge_Data()
    Dim myFiles As Variant
    Dim myCountOfFiles As Long

    myCountOfFiles = Get_File_Names( _
                     MyPath:="C:\Users\Ron\test", _
                     Subfolders:=False, _
                     ExtStr:="*.xl*", _
                     myReturnedFiles:=myFiles)

    If myCountOfFiles = 0 Then
        MsgBox "No files that match the ExtStr in this folder"
        Exit Sub
    End If

    Get_Data _
            FileNameInA:=True, _
            PasteAsValues:=True, _
            SourceShName:="", _
            SourceShIndex:=1, _
            SourceRng:="A1:G1", _
            StartCell:="", _
            myReturnedFiles:=myFiles

End Sub

First we call the function "Get_File_Names" to fill a array with all file names
There are three arguments in this function that we can change.

1) MyPath
The folder where the files are
Note: There is also a macro example "RDB_Merge_Data_Browse" that let you browse to the folder

2) Subfolders
True if you want to include subfolders

3) ExtStr
File extension of the files you want to merge.
Examples are: "*.xls" , "*.csv" , "*.xlsx"
"*.xlsm" ,"*.xlsb" , for all Excel file formats use "*.xl*"


Then if there are files in the folder we call the macro "Get_Data"
There are six arguments in this macro that we can change.

1) FileNameInA
True to add the path/file name in the A column

2) PasteAsValues
True to paste as values (recommend)

3) SourceShName
Enter the name of the sheet that you have in every workbook
If "" it will use the SourceShIndex and if "all" it copy from all worksheets

4) SourceShIndex
To avoid problems with different sheet names you can use the index
If you use 1 it use the first worksheet in each workbook.

5) SourceRng
Range you want to copy. Tip: "A:F" will copy all cells with data in this six columns

6) StartCell
Enter the first cell and the macro will copy from that cell till the last cell on the worksheet
If StartCell = "" then it use the SourceRng

After you make your changes run the macro in the VBA editor or with Alt F8 in Excel
It will add a new workbook with the data for you and it is up to you if you want to save it.




Copy sheet from each workbook

Note: This is only the macro that you must change and not all the code that you need.
Download the example workbook to test the code.

This example below will copy the first worksheet from each file in the folder to a new workbook.

Sub RDB_Copy_Sheet()
    Dim myFiles As Variant
    Dim myCountOfFiles As Long

    myCountOfFiles = Get_File_Names( _
                     MyPath:="C:\Users\Ron\test", _
                     Subfolders:=False, _
                     ExtStr:="*.xl*", _
                     myReturnedFiles:=myFiles)

    If myCountOfFiles = 0 Then
        MsgBox "No files that match the ExtStr in this folder"
        Exit Sub
    End If

    Get_Sheet _
            PasteAsValues:=True, _
            SourceShName:="", _
            SourceShIndex:=1, _
            myReturnedFiles:=myFiles

End Sub

First we call the function "Get_File_Names" to fill a array with all file names
Read the information in the first example on the page.

Then if there are files in the folder we call the macro "Get_Sheet"
There are three arguments in this macro that we can change

1) PasteAsValues
True to paste as values (recommend)

2) SourceShName
Enter the name of the sheet that you have in every workbook
If "" it will use the SourceShIndex

3) SourceShIndex
To avoid problems with different sheet names you can use the index
If you use 1 it use the first worksheet in each workbook.




Copy the filter results from a sheet from each workbook

Note: This is only the macro that you must change and not all the code that you need.
Download the example workbook to test the code.

The example below will filter the A column of the first worksheet from
each file for "ron" and copy the results to a new workbook

Sub RDB_Filter_Data()
    Dim myFiles As Variant
    Dim myCountOfFiles As Long

    myCountOfFiles = Get_File_Names( _
                     MyPath:="C:\Users\Ron\test", _
                     Subfolders:=False, _
                     ExtStr:="*.xl*", _
                     myReturnedFiles:=myFiles)


    If myCountOfFiles = 0 Then
        MsgBox "No files that match the ExtStr in this folder"
        Exit Sub
    End If

    Get_Filter _
            FileNameInA:=True, _
            SourceShName:="", _
            SourceShIndex:=1, _
            FilterRng:="A1:D" & Rows.Count, _
            FilterField:=1, _
            FilterValue:="ron", _
            myReturnedFiles:=myFiles

End Sub

First we call the function "Get_File_Names" to fill a array with all file names.
Read the information in the first example on the page.

Then if there are files in the folder we call the macro "Get_filter"
There are six arguments in this macro that we can change

1) FileNameInA
True to paste the file name in the A column (recommend)

2) SourceShName
Enter the name of the sheet that you have in every workbook
If "" it will use the SourceShIndex

3) SourceShIndex
To avoid problems with different sheet names you can use the index
If you use 1 it use the first worksheet in each workbook.

4) FilterRng
Range that you want to filter: A1 is the header of the first column and D is the last column
in this example. Because I use & Rows.Count it will use all rows on the sheet.
You can also use a fixed range if you want like FilterRng:="A1:D3500"

5) FilterField
The filter range start in column A so field 1 = column A and field 3 = column C

6) FilterValue
Use "<>ron" if you want the opposite
Or use wildcards like "*ron" for cells that start with ron or use
'"*ron*" if you look for cells where ron is a part of the cell value




More Information

Another way is to use this add-in: RDBMerge
http://www.rondebruin.nl/merge.htm

For other code examples see my Dir page
http://www.rondebruin.nl/copy3.htm

Use ADO to copy from closed workbooks
http://www.rondebruin.nl/ado.htm

Merging Data from Multiple Workbooks into a Summary Workbook in Excel
http://msdn.microsoft.com/en-us/library/cc837974.aspx