Excel VBA CodeTutorials

Loop Multiple Sheets and Merge Data using Excel VBA Worksheets

2 Mins read

In this blog article, we will be covering how to loop through multiple sheets and merge data in Excel VBA Worksheets as per our requirement. Just imagine you have a lot of files in a folder and for each file, you have 3 sheets.

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.

Let’s Start how to merge data in VBA Worksheets.

Now If I ask you to prepare a final master file which will be a combination of all the files and all the sheets (Merge Data). Making you clearer on this. In my data folder, I have two files, as you can see in the below screenshot.


When I open a file I get 3 sheets like 1, 2, and 3… (Refer screenshot below) for all the files, It’s the same in all the spreadsheets. Now I have another file called Master where I need to merge all the sheets. Not in single sheets but for Master sheet also I have 3 sheets as the heading is not the same for all the sheets.

excel vba tricks

Now if you are asked to prepare a master file where all you need to do is you will have to do the following manually. Open your master file:

  • Step 1: Go to the folder and open the first file.
  • Step 2: Then go to the first sheet of the file and copy the data.
  • Step 3: Go to your master file and then select the First sheet.
  • Step 4: Finally, paste the data.

Similarly, you will have to do the same thing for the other file also. Now think of that situation where there are 100 files in your folder and the 50 sheets are there in each file. If I take 30 secs to copy and paste, then it will take 30 * 50 = 1500 secs for a single file and for 100 files ??? Oh!!! It is too boring!!! But if you know the code it will take few minutes to complete the job and the most important thing is that you don’t have to do anything. Your code will do the trick for you to merge data in the master file.

Here are the steps to do it automatically:

  • Step 1: Open Master File.
  • Step 2: Put the header of all the sheets.
  • Step 3: Press Alt + F11 to go to the VBA page.
  • Step 4: Go to Insert and then Module.
  • Step 5: Copy the below code and then paste in the Module.
  • Step 6: Finally, run the code by pressing F5.
Sub MergeWbooksAndSheets()
Dim wbk As Workbook
Dim sht As Worksheet
Dim shtt As Worksheet
Dim sheetfirst As Worksheet
Dim sheetsecond As Worksheet
Dim sheetthird As Worksheet
Dim wbk2 As Workbook
Set wbk2 = ThisWorkbook
Dim Filename As String
Dim Path As String
Path = "C:\Personal\Data\" 'CHANGE Folder PATH
Filename = Dir(Path & "*.xlsx")
Do While Len(Filename) & 0 'IF NEXT FILE EXISTS THEN
Set wbk = Workbooks.Open(Path & Filename)
For Each shtt In wbk.Worksheets
Var = shtt.Name
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Dim lr As Integer
'Var = sht.Name
lr = wbk2.Sheets(Var).Cells(Rows.Count, 1).End(xlUp).Row
Cells(lr + 1, 1).Select
wbk.Close True
Filename = Dir
End Sub

Your VBA Worksheets code will look like below:


You will notice that the code will go to that directory, open the excel file, and copying and paste the data one by one from the sheets. Finally, you will get the result in your master sheet. Please download the sample workbook including the sample files, check the code, and change the directory path so that it can run.

Further, we have already covered the blog on How to Merge Excel Files into a Spreadsheet using 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

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…

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…

1 Comment

Comments are closed.