VBA Macro For Merging Worksheets

No Comments
 VBA Macro For Merging Worksheets
Here is a three-step guide to combine all the data of all the sheets to single sheet. Follow these steps as mention: -
1.  Press ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window or you can open Microsoft Visual Basic window from developer tab >>Macros (in Code Group) as shown picture. (step by step guide about to add developer tab Click here).

2. Copy the following VBA and Click Insert > Module, and paste the following code in the Module Window.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub


3. Now press F5 key to run this code, and it’s all done. A new worksheet named Combined has been created in which all the data from all the worksheets in the workbook has been merged and this worksheet will add before all worksheets.

Notes: -
  • Your data must start from A1, if not, the code will not take effect.
  • Your data must have the same structure.
  • This code only can combine all worksheets of the active workbook, if you want to merge worksheets from multiple workbooks, <<< click here >>>



VBA Macro For Merging Workbooks

No Comments
VBA Macro For Merging Workbooks
 Here is a five-step guide to combine all sheets of different workbooks to a single workbook. Follow these steps as mention: -

1. First of all put all the workbooks that you want to combine into the one folder as shown in screenshot:-

2. Launch an Excel file that you want to combine other workbooks into.

3. Press ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window or you can open Microsoft Visual Basic window from developer tab >>Macros (in Code Group) as shown picture. (step by step guide about to add developer tab Click here)

4. Copy the following VBA and Click Insert > Module, and paste the following code in the Module Window and change the path from “C:\Users\ankit\Desktop\EWE” to the path of the folder in which all the files have been moved/copied.
1
2
3
4
5
6
7
8
9
10
11
12
Sub GetSheets()
Path = "C:\Users\ankit\Desktop\EWE\"
Filename = Dir(Path & "*.xlsx")
  Do While Filename <> ""
  Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
     For Each Sheet In ActiveWorkbook.Sheets
     Sheet.Copy After:=ThisWorkbook.Sheets(1)
  Next Sheet
     Workbooks(Filename).Close
     Filename = Dir()
  Loop
End Sub












  5.  Now press F5 key to run this code, and it’s all done. All of the worksheets (including the blank worksheets) within the workbooks have been merged into the active workbook.

Note: -

  • The workbook in which you want to combine all the worksheets and all other workbooks must not be in one folder. (Tip:- use this macro in unsaved new workbook)
  • This VBA code can merge the different workbooks into the single workbook, if you want to combine specified worksheets of the workbook then <<click here>>.


Amazon

Is this article helpful to you?