combine several workbooks into one

Snabelhund

New Member
Joined
Nov 11, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
Hi!
i would need some help with a macro that combines several workbooks into one master workbook. Also the macro should be movable between computers (will need to run it on several different computers) so preferably written as an add-in, but any solution that is "portable " will work really.

So i´ve started to write a piece but im stuck so id appreciate some help. tried seraching both the forum and the web but i cant really find a good solution for this

Basically what ive tried is to write some code where the user can select the location for the source files (the files that are going to be consolidated into one workbook) using the Application.FileDialog(msoFileDialogFolderPicker) But i´m struggling with the rest, I guess that i should loop through all the workboks in the selected folder but i´m struggling with the code.

So to clarify what i want to achieve is:
1. let the user select a location/folder with workbooks to combine/consolidate. (for clarification these are called source workbooks)
2. copy all the "source workbooks into one workbook.

All the data in the source workbooks is stored in the first sheet and thet workbooks all have the same layout/structure

Really greatful for some help!

Cheers
/N
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
is the data on your source workbooks, is spread over multiple worksheets or does each workbook contains only a single worksheet with data? Also what is the layout structure of your source workbooks?
 
Upvote 0
is the data on your source workbooks, is spread over multiple worksheets or does each workbook contains only a single worksheet with data? Also what is the layout structure of your source workbooks?
the sourceworkbooks contains only one sheet of data and the structure is basically data in columns A to L. Altough this can differ somewhat with headers and populated columns.

The column length is always the same for all the columns in one workbook but can differ between workbooks.

For example "sourceworkbook 1" can have data in columns A to L and down to row 15 wheras "sourceworkbook 2" will have data stored in same number of columns but the last data row is 75
 
Upvote 0
Create a Master Workbook and try the following:

VBA Code:
Sub open_file()
Dim file_path As Variant
Dim file_name As String
Dim s_rng As Range
Dim Ans As String

Application.DisplayAlerts = False

Process:
file_path = Application.GetOpenFilename(FileFilter:="Excel Files,*.xl*")

If file_path = False Then
    MsgBox "Workbook not selected. Please select a workbook to proceed.", vbCritical
Else
    Workbooks.Open Filename:=file_path
End If

file_name = ActiveWorkbook.Name

With Workbooks(file_name)
    With Sheets("sheet1")
        lr = .Cells(.Rows.Count, 1).End(xlUp).Row
        lc = .Cells(1, .Columns.Count).End(xlToLeft).Column
        Set s_rng = .Range(.Cells(2, 1), .Cells(lr, lc))
        s_rng.Copy
    End With
    lr = ""
    lc = ""
    
End With

With Workbooks("MasterWorkbook.xlsm")
    
        .Activate
        With Sheets("sheet1")
            lr = .Cells(.Rows.Count, 2).End(xlUp).Row
            .Cells(lr + 1, 2).PasteSpecial Paste:=xlPasteValues
        End With

End With

Workbooks(file_name).Close

Ans = MsgBox("Do you want to select another workbook?", vbYesNo, "???")

If Ans = vbYes Then
    GoTo Process
Else
    MsgBox "Processing Complete."
End If

file_path = ""
file_name = ""
Set s_rng = Nothing
Ans = ""

Application.DisplayAlerts = True
End Sub


I used some sample HR data found through google, since actual piece of data was not available. Created a Master Workbook containing the Macro and used various other workbooks with sample data. The structure of all of these workbooks is same. I have shared all of these here. You can amend the code to best suite your needs.

hth...
 
Upvote 0
Solution

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top