In this post we are going to see how to merge the multiple Excelsheets data in to one Excelsheet using VBA Macro, sometimes there is a scenario where you have same structured data in multiple Excelsheets, you may want that data to collate in to one excelsheet, To do this we are going to do a programming which will have two buttons , one is for the browse the folder where you can find the all Excelsheets which are need to merge under one folder path, next button is used to merge.
First we have to check whether Excel is a macro enabled , to do this we have enable macro using the following steps in mentioned in the below url.
http://dotnetvisio.blogspot.in/2016/11/how-to-enable-developer-tab-in.html
Then now need to Developer Tab and click on the Activex Controls and Drag the Two buttons, Then right click on the both the buttons and edit the name of that, Then we have to double click on each button and start writing the code.
First we have to browse a folder where we are kept the files which are needs to merge., Let we start write the code for Browse.
Code for Browse:
*********************
Click on the Merge button, and start typing the code for the Merge.
Code for Collate
**********************
Click on the browse button and select the folder, You can see this folder have three excel files, data1.xlsx, data2.xlsx, data3.xlsx
Click in the Merge button, Give the name of the excel file as "MergeData" in which you have to save the collated data, thats it, All data are merged in one excel, in this sample we are collating three Excel files
Data1.xlsx
Data2.xlsx
Data3.xlsx
Output:
From this post you can see how to create a program in excel to collate a data from multiple excel file to one excel file
First we have to check whether Excel is a macro enabled , to do this we have enable macro using the following steps in mentioned in the below url.
http://dotnetvisio.blogspot.in/2016/11/how-to-enable-developer-tab-in.html
Then now need to Developer Tab and click on the Activex Controls and Drag the Two buttons, Then right click on the both the buttons and edit the name of that, Then we have to double click on each button and start writing the code.
First we have to browse a folder where we are kept the files which are needs to merge., Let we start write the code for Browse.
Code for Browse:
*********************
Private Sub CommandButton1_Click() Browse End Sub
Sub Browse() Dim folderdlg As FileDialog Set folderdlg = Application.FileDialog(msoFileDialogFolderPicker) folderdlg.AllowMultiSelect = True folderdlg.Show If folderdlg.SelectedItems.Count > 0 Then folderPath = folderdlg.SelectedItems(1) End If End Sub
Click on the Merge button, and start typing the code for the Merge.
Code for Collate
**********************
Private Sub CommandButton2_Click() Merge End Sub Sub Merge() Dim ProBook As Workbook Dim mSheet As Worksheet Dim cFileName As String Dim savefilepath As String Dim cRow, cColumn As Integer Dim pRow, pColumn As Interior Dim firsttime As Boolean Dim filedlg As FileDialog If folderPath = "" Then MsgBox "Please select the folder, for merge the files, then click Merge" End If If folderPath <> "" Then folderPath = folderPath & "\" Set filedlg = Application.FileDialog(msoFileDialogSaveAs) filedlg.Show firsttime = True If filedlg.SelectedItems.Count > 0 Then savefilepath = filedlg.SelectedItems(1) cFileName = Dir(folderPath & "*.xl*") Set mSheet = Workbooks.Add(XlWBATemplate.xlWBATWorksheet).Worksheets(1) cRow = 1 cColumn = 1 Do While cFileName <> "" Set ProBook = Workbooks.Open(folderPath & cFileName) Dim LastRow As Long Dim lastCol As Long Dim srcRange As Range Dim dstRange As Range lastCol = ProBook.Worksheets(1).Cells(1, Columns.Count)_ .End(xlToLeft).Column LastRow = ProBook.Worksheets(1).Cells.Find(What:="*", _ After:=ProBook.Worksheets(1).Cells.Range("A1"), _ SearchDirection:=xlPrevious, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows).Row Dim col_letter As String col_letter = Column_Letter(lastCol) If firsttime Then Set srcRange = ProBook.Worksheets(1).Range("A1:" & col_letter & LastRow) Else Set srcRange = ProBook.Worksheets(1).Range("A2:" & col_letter & LastRow) End If Set dstRange = mSheet.Range("A" & cRow) Set dstRange = dstRange.Resize(srcRange.Rows.Count, _ srcRange.Columns.Count) ' Copy over the values from the source to the destination. dstRange.Value = srcRange.Value ' Increase row so that we know where to copy data next. cRow = cRow + dstRange.Rows.Count ' Close the source workbook without saving changes. ProBook.Close savechanges:=False firsttime = False ' Use Dir to get the next file name. cFileName = Dir() Loop End If mSheet.Columns.AutoFit mSheet.SaveAs (savefilepath) End If End Sub Function Column_Letter(lngCol As Long) As String Dim vArr vArr = Split(Cells(1, lngCol).Address(True, False), "$") Column_Letter = vArr(0) End Function
Click on the browse button and select the folder, You can see this folder have three excel files, data1.xlsx, data2.xlsx, data3.xlsx
Click in the Merge button, Give the name of the excel file as "MergeData" in which you have to save the collated data, thats it, All data are merged in one excel, in this sample we are collating three Excel files
Data1.xlsx
Data2.xlsx
Data3.xlsx
Output:
From this post you can see how to create a program in excel to collate a data from multiple excel file to one excel file