How to combine all workbook into one in excel?
In Excel if you have many workbooks need combine into one then below code will do the job easily. Now copy below code and paste into module:
Option Explicit Sub CombineAllWorkBook_IntoOne() 'This code was created by http://vbexcel.com 'Written by Hisham Hadi Dim msg As String Dim FileArray As Variant Dim myB As Workbook Dim myNB As Workbook Dim j As Integer 'Create new workbook single sheet Set myNB = Workbooks.Add(1) FileArray = Application.GetOpenFilename(MultiSelect:=True) If IsArray(FileArray) Then Application.ScreenUpdating = False For j = LBound(FileArray) To UBound(FileArray) Set myB = Workbooks.Open(FileArray(j)) myB.Worksheets.Copy After:=myNB.Worksheets(j) myB.Close False Next j 'To delete empty sheets added Application.DisplayAlerts = False Sheets("Sheet1").Delete Application.DisplayAlerts = True Application.ScreenUpdating = True Sheets(1).Select Range("A1").Select Else: MsgBox "No file was selected." myNB.Close False End If End Sub
For the above code actually when you run this macro it automatically browse folder ask user to select any folder and file inside(multiple file) by pressing shift or Ctrl button and click any file. When user click open it will combine all selected file into one file. Done
I had tested for excel 2003 and also excel 2007 and the result was OK. Let’s try and give your feedback..
Click here if you have any new project! Let us create for u for free.