Or if you want all the options in a nice user interface check ou this add-in : RDBMerge, Excel Merge Add-in for Excel for Windows There are a few things you must change before you can run the code Fill in the path to the folder Note: the code above use the function RDB_Last, copy this function also in your code module if you use it. It is also possible to set the start folder with Ch Drive and Ch Dir but I choose to use the Set Current Directory A function in this example because it also is working with network folders. Get Open Filename(filefilter:="Excel Files (*.xl*), *.xl*", _ Multi Select:=True) If Is Array(FName) Then For Fnum = LBound(FName) To UBound(FName) Set mybook = Nothing On Error Resume Next Set mybook = Workbooks. Number With source Range Set destrange = destrange. Column On Error Go To 0 Case 3: On Error Resume Next lrw = rng.

You find the function in the last section of this page. Note: Copy all code below in a normal module of your workbook #If VBA7 Then Declare Ptr Safe Function Set Current Directory A Lib _ "kernel32" (By Val lp Path Name As String) As Long #Else Declare Function Set Current Directory A Lib _ "kernel32" (By Val lp Path Name As String) As Long #End If Sub Ch Dir Net(sz Path As String) Set Current Directory A sz Path End Sub Sub Basic_Example_2() Dim My Path As String Dim Source Rcount As Long, Fnum As Long Dim mybook As Workbook, Base Wks As Worksheet Dim source Range As Range, destrange As Range Dim rnum As Long, Calc Mode As Long Dim Save Drive Dir As String Dim FName As Variant With Application Calc Mode = . Open(FName(Fnum)) On Error Go To 0 If Not mybook Is Nothing Then On Error Resume Next With mybook. Find(What:="*", _ after:=rng.cells(1), _ Lookat:=xl Part, _ Look In:=xl Formulas, _ Search Order:=xl By Rows, _ Search Direction:=xl Previous, _ Match Case:=False).

macro consolidating workbooks-73

Copy the code in a Standard module of your workbook, if you just started with VBA see this page.

Where do I paste the code that I find on the internet Code Examples that use DIRThere are four basic examples, 3 on this page and 4 in the example workbook:1) Merge a range from all workbooks in a folder (below each other) 2) Merge a range from every workbook you select (below each other) 3) Merge a range from all workbooks in a folder (next to each other)4) Merge a range from all workbooks in a folder with Auto Filter The code will create a new workbook for you with the data from all workbooks with in column A or in row 1 the file name of the data in that row or column. The examples below are only working for one folder, there is no option for subfolders.

There is more code in this file but the macro we call is easier to edit. Value rnum = rnum Source Rcount End If End If mybook. There are a few things you must change before you can run the code Fill in the path to the folder Sub Basic_Example_3() Dim My Path As String, Files In Path As String Dim My Files() As String Dim Source Ccount As Long, Fnum As Long Dim mybook As Workbook, Base Wks As Worksheet Dim source Range As Range, destrange As Range Dim Cnum As Long, Calc Mode As Long 0 Then For Fnum = LBound(My Files) To UBound(My Files) Set mybook = Nothing On Error Resume Next Set mybook = Workbooks. Number With source Range Set destrange = destrange. Row On Error Go To 0 Case 2: On Error Resume Next RDB_Last = rng.

Download Merge FSOExamples.zip, you can also download the FSO code as TXT file. Close savechanges:=False End If Next Fnum Base Wks. Auto Fit End If Exit The Sub: And change the sheet and range to yours (see first example). Enable Events = False End With Save Drive Dir = Cur Dir Ch Dir Net "C:\Users\Ron\test" FName = Application. Open(My Path & My Files(Fnum)) On Error Go To 0 If Not mybook Is Nothing Then On Error Resume Next Set source Range = mybook. Find(What:="*", _ after:=rng.cells(1), _ Lookat:=xl Part, _ Look In:=xl Formulas, _ Search Order:=xl By Columns, _ Search Direction:=xl Previous, _ Match Case:=False).

First, lets take a look at the consolidate data VBA code. Since we are using the same instance of Excel we may allow the user to preserve the format of the data being pasted. Allow the user with the option to clear data before new is pasted. Please Note: You would need to create the data files on your system, this download only contains the code template to consolidate.

There is one master file (or sheet) which needs to be consolidated by pulling data from multiple source files containing raw data (having the same data structure). If you are new to VBA, Excel macros, go thru these links to learn more. Open(My Path & My Files(Fnum)) On Error Go To 0 If Not mybook Is Nothing Then On Error Resume Next With mybook. Column On Error Go To 0 On Error Resume Next RDB_Last = rng. David has several workbooks that have several worksheets in each of them.Sub Get Data() Dim str Where To Copy As String, str Start Cell Col Name As String Dim str List Sheet As Stringstr List Sheet = “List” On Error Go To Err H Sheets(str List Sheet). Select ‘this is the main loop, we will open the files one by one and copy their data into the masterdata sheet Set current WB = Active Workbook Do While Active Cell. If you want to learn how to develop applications like these and more, please consider joining our VBA Classes. I have also made use of UDF (user defined function) to find the last cell in the column that we specify.At Chandoo.org, I have one goal, "to make you awesome in excel and charting".