Merging Workbooks in Excel Automatically Using VBA

Checking data from different workbooks is such a heckting work, but what happen when you put data at one place in a single sheet automatically.

Yes, you listen it write. That is possible to collect all data at one place. We can do this with the help of VBA.

All you need to do is to follow the below steps. It's that simple. But this work only for excel which are in ".CSV" format.

But not to worry, Soon I'll come up with an update for ".xlsx" format also.

Steps to Run macro for Files merging

Purpose: It will merge all the data from various workbooks as one place in the single sheet.

Benefits: 

1. It will help to anlyse and check the data of multiple workbook at one place.
2. It will save time on opening files and apply the process of verification on multiple files.

Run macro for Files merging:

Step-1

Open excel sheet.

Step-2

Open visual basic to create macro module.

1. To opne visual basic, click on developer tab in your excel toolbar option.
2. Now, Click on visual basic option.
3. New window open when you click on visual basic option.

(Note: To open this visual basic window you can also use shortcut key like “Alt+F11”)

Step-3

Now when this window appears, click on insert & then module.

Step-4

After you click on insert & module a new window will appear within this window.

Step-5

Copy the below code & paste it in this new window.

Copy the cobe from below.

Sub MergeDataFromFolder()

Dim copiedsheetcount As Long

Dim rowcnt As Long

Dim merged As Workbook

Dim wb As Workbook

Dim ws As Worksheet

filefolder = "D:\Files\"

Filename = Dir(filefolder & "*.CSV")

If Filename = vbNullString Then

MsgBox prompt:="No File", Buttons:=vbCritical, Title:="error"

Exit Sub

End If

copiedsheetcount = 0

rowcnt = 1

Set merged = Workbooks.Add

ActiveSheet.Name = "Merged Data"

Do While Filename <> vbNullString

copiedsheetcount = copiehsheetcount + 1

Set wb = Workbooks.Open(Filename:=filefolder & Filename, UpdateLinks:=False)

Set ws = wb.Worksheets(1)

With ws

If FilterMode Then .ShowAllData

If copiedsheetcount > 1 Then .Rows(1).EntireRow.Delete shift:=xlUp

.Range("a1").CurrentRegion.Copy Destination:=merged.Worksheets(1).Cells(rowcnt, 1)

End With

wb.Close savechanges:=False

rowcnt = Application.WorksheetFunction.CountA(merged.Worksheets(1).Columns("A:A")) + 1

Filename = Dir

Loop

MsgBox prompt:="File Merged", Buttons:=vbInformation, Title:="Success"

End Sub

The Code ends at "End Sub" above.

Step-6

Change the folder path in the code.

1.  To change the folder path code, go the files where all your files are kept for merger.

(Note: All your files must be in .CSV format in the folder. and there is no blank row or column between the data. Other wise the programm only work for data which is before that blank row or column.)

1. Copy the folder path.

Ex: C:\Users\Ankit\Downloads\ankit merging file

(Note: The folder path has a missing slash (“\”)  at the end, So we have to add this. And also don’t forget the Quotation mark (“”).)

1. Paste this copied link in the module at their respective place. As shown below.

filefolder = "D:\Files\"

Example:

Wrong: C:\Users\Ankit\Downloads\ankit merging file

Correct: C:\Users\Ankit\Downloads\ankit merging file\

Step-7

Now It time to run the code for merging all your files in the folder in the single sheet.

1. To run the code “Press F5” or click on the run command in the option tool.
2. Either it will run directly or open up with an option window.
3. Press the “Run” button in the new window appear.

Step-8

Now you have single excel sheet with all the merged data from different workbook to analyze and check at one place.