Loop Multiple Sheets and Merge Data using Excel VBA Worksheets

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.

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. Picture1

(Picture 1)

When I open a file I get 3 sheets like 1, 2 and 3… (Refer screenshot below) for all the files, It’s 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

(Picture 2)

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:

  1. Go to the folder and open the first file.
  2. Then go to the first sheet of the file and copy the data.
  3. Go to your master file and then select the First sheet.
  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:

  1. Open Master File.
  2. Put the header of all the sheets.
  3. Press Alt + F11 to go the VBA page.
  4. Go to insert and then Module.
  5. Copy the below code and then paste in the Module.
  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")

'OPEN EXCEL FILES

Do While Len(Filename) > 0 'IF NEXT FILE EXISTS THEN

Set wbk = Workbooks.Open(Path & Filename)

wbk.Activate

For Each shtt In wbk.Worksheets

wbk.Activate

Var = shtt.Name

shtt.Select

Range("A2").Select

Range(Selection, Selection.End(xlToRight)).Select

Range(Selection, Selection.End(xlDown)).Select

Selection.Copy

Windows("Master.xlsm").Activate

Dim lr As Integer

'Var = sht.Name

Sheets(Var).Select

lr = wbk2.Sheets(Var).Cells(Rows.Count, 1).End(xlUp).Row

Cells(lr + 1, 1).Select

ActiveSheet.Paste

Next

wbk.Close True

Filename = Dir

Loop

End Sub

Your VBA Worksheets code will look like below:

merge data in excel vba

(Picture 3)

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

1 Response
  1. Hussaan

    The above code is very useful to merge data. I need help for How to make the code workable if folder name is fix and computer drive location is not fix due to files folder on a network location or USB storage plug in various computers. Kindly recommend the changes?

Related Tutorials

Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
OFFSET Function in Excel
July 6, 2018
Strikethrough Shortcut in Excel & Word
July 4, 2018
INDIRECT Function with SUM, MAX, MIN & Independent Cell Value
June 29, 2018
Pivot Table Slicers In Excel
June 12, 2018
How to Split Cells in Excel using Text to Column
June 7, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
How to Hide/Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
Learn how to remove blank cells in Excel
June 3, 2018
How to Group Numbers, Dates & Text in Pivot table in Excel
June 1, 2018
5 Powerful Tricks to Format cells in Excel
May 31, 2018
Insert a Picture into a Cell in Excel
May 25, 2018
What is ISFORMULA Function and FORMULATEXT Function
May 21, 2018
How to Use SUBSTITUTE Function
May 21, 2018
Excel Quartile Function in Excel
May 8, 2018
How to use the Excel PERCENTILE function
May 7, 2018
Insert or Type degree symbol in Excel with Autocorrect Feature
May 7, 2018
25% Discount
No prize
All Courses at $200
Almost!
10% Discount
Free Ebook
No Prize
No luck today
Almost!
50% Discount
No prize
80% Discount
Get a chance to boost your knowledge!
Use this coupon code for any course that you wish for. 
Our in-house rules:
  • You can choose any course & redeem coupon
  • If you find any difficulty, mail us on [email protected]
  • Wheel Spin will end soon
  • Coupon code can be applied within 2 days.