2022 updates MS Excel - Data Cleaning Course Enroll now  
Excel VBA CodeTutorials

How to Merge Excel Files in to one Or into a Spreadsheet using Excel Macro

2 Mins read

How to merge excel files or combine excel files into a single sheet from a different workbook using excel macro is the topic of this blog. But in the earlier blog, we have covered how to merge data in a single sheet when the data are coming from the same sheet. We are trying to cover techniques to combine excel files in a single sheet when the data is saved in separate files in a location.

Now think if you open the file one by one and manually combine excel files, how long time will it take? At the same time, a small piece of macro code will do the trick to merge excel files for you even though you are not a programmer. All you need to know is how to paste the VBA code and run it.

Excel VBA Macros for Non-Coders

Do you find VBA Macros scary? Did you miss any job opportunity because of it? Get started today with our eBook guide on using – Excel VBA Macros. 140 pages of rich visuals. Download now.

How to Merge excel files in Excel using excel macro:

First of all, Now save the files in one Drive/Folder and link to all practice files used in our example D:\Collate Multiple Files. It’s necessary to keep all the files in one location to merge excel workbooks into a single spreadsheet There are lots of files. We need to merge the same into a single spreadsheet. Make sure the format or the heading of all the files is the same.

I have created a dummy book called Book1 in the same place. I will merge all the data in this book.

Steps to combine excel into a single file:

  1. Open the workbook called Book1
  2. Press Alt + F11 to the VBA page
  3. Click on Insert and then Module
  4. Copy and paste the below macro code
Sub MergeDataFromWorkbooks()

'DECLARE AND SET VARIABLES
Dim wbk As Workbook
Dim wbk1 As Workbook
Set wbk1 = ThisWorkbook
Dim Filename As String
Dim Path As String

Path = "D:\Collate Multiple Files\" 'CHANGE PATH

Filename = Dir(Path & "*.xlsx")
'OPEN EXCEL FILES

Do While Len(Filename) > 0 'IF NEXT FILE EXISTS THEN
    Set wbk = Workbooks.Open(Path & Filename)
    wbk.Activate
    Range(“A2”).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows(“Book1”).Activate
    Application.DisplayAlerts = False
    Dim lr As Double
    lr = wbk1.Sheets(“Sheet1”).Cells(Rows.Count, 1).End(xlUp).Row
    Sheets(“Sheet1”).Select
    Cells(lr + 1, 1).Select
    ActiveSheet.Paste
    wbk.Close True
    Filename = Dir
Loop
MsgBox "All the files are copied and pasted in Book1."
End Sub

After pasting the code, please press F5 to run the excel macro code. It will open a file and then copy the data and paste the same in Book1 and close the workbook.

Now, please check the same whether the code is working perfectly or not. First of all, have a look at the Book1 file. It looks like below and there is no data resides in the book except the header.

Code-working-perfect-Excel-Macro

Press Alt + F11 and paste excel macro code

Excel-Macro-Code

Press F5 to run the excel macro code.

It will then open the file one by one and also, paste the data in Book1. After running the loop, it will give you a message that it has successfully copied and pasted the data in Book1.

Sucessfully-copied-in-Sheet-Excel-Macro

Excel VBA Macros for Non-Coders

Do you find VBA Macros scary? Did you miss any job opportunity because of it? Get started today with our eBook guide on using – Excel VBA Macros. 140 pages of rich visuals. Download now.

Related posts
AlteryxTutorials

Alteryx Hotkeys - Alteryx Keyboard Shortcuts

2 Mins read
Top 50+ Alteryx Shortcuts for Windows. Alteryx is popularly known as a Self-Service Analytics tool. Business users can build their data workflows…
AlteryxTutorials

Step By Step Guide to Learn Alteryx

6 Mins read
Alteryx Learning Path: The growth in technology has resulted in growth in understanding. In today’s world, humans – fellow businessmen know the…
Excel VBA CodeTutorials

VBA Code to Clean the Date Format

1 Mins read
When it is useful? Most of the time the most annoying problem is when the data is taken from ERP or other…

24 Comments

Comments are closed.