Resources

    All about Visual Basic for Application (VBA) especially for Microsoft Excel.

Subscribe

  • Subscribe

how to combine selected file in excel into one using vba?

Posted by | June 30, 2011 .

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.
Thanks
Good luck

Share Button
Share on Facebook
Bookmark this on Digg
Bookmark this on Yahoo Bookmark
Bookmark this on Google Bookmarks
Bookmark this on Delicious

Leave a Comment

If you would like to make a comment, please fill out the form below.

Name (required)

Email (required)

Website

Comments