@SahadebPatro , create a file like this:
Paste the below macro into it starting with "Sub CombineExcelFiles()" all the way to the end. The assumptions built into this macro are that every workbook being combined has the same number of tabs, each tab has the same number of columns in the same order, and every tab has a title row. If none of these assumptions are correct, you will have to play with it a little. For example, if there is not a title row, change the red line below to read 'Rows("1:" & LR).Copy' instead of 'Rows("2:" & LR).Copy'. Otherwise, you simply list all your excel files in A:B, and where you want the final product saved in E:F. Best of luck!
Sub CombineExcelFiles()
'
' CombineExcelFiles Macro
'
'
'get loop max (last row of filenames)
Dim LoopMax As Long
Range("A1").Select
Selection.End(xlDown).Select
LoopMax = Selection.Row
'Define the string variables filepath and filename
Dim strfilepath As String
Dim strfilename As String
'i is the loop index, we start at 2 and work until loop max; LR is last row, used throughout the loop; NWB is New Workbook used through Loop
Dim NWB As Workbook
Dim LR As Long
Dim i As Long
i = 2
Do
'define the filepath and filename
Workbooks("CombineExcelFiles.xlsm").Activate
strfilepath = Range("A" & i)
strfilename = Range("B" & i)
'open the workbook
ChDir (strfilepath)
Workbooks.Open filename:=strfilepath & "\" & strfilename
'define sheet variables for later use
Dim AMWS As Worksheet 'active master worksheet
Dim ANWS As Integer 'active new worksheet to be copied to active master worksheet, but by index
'If the LoopIndex (i) = 2, declare Master WorkBook (MWB)
If i = 2 Then
'indentify master workbook name as this will become the master for all other workbooks to be copied to
Dim MWB As Workbook
Set MWB = ActiveWorkbook
Sheets(1).Activate 'make sure we are on tab 1
Else 'if i <> 2
'copy the rows from the new workbook and paste them in the master workbook
'define New WorkBook (NWB)
Set NWB = ActiveWorkbook
'reactivate the master workbook
Workbooks(MWB.Name).Activate
'for each sheet in workbook (this assumes all combined workbooks have the same tabs and the same column set in each tab)
For Each AMWS In MWB.Worksheets ' for each worksheet in the master workbook
ANWS = AMWS.Index 'the active new worksheet is indexed by the active master worksheet
Workbooks(NWB.Name).Activate
Sheets(ANWS).Activate
'get the last row (LR) of the new workbook
Range("A1").Select
Selection.End(xlDown).Select
LR = Selection.Row
'assuming a title row, copy all rows 2 through last row
Rows("2:" & LR).Copy
'go to master workbook
Workbooks(MWB.Name).Activate
Sheets(AMWS.Index).Activate
'select first available cell in column A
Range("A1").Select
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select
'paste all copied data in first available cell in column A
ActiveSheet.Paste
'remove alerts to stop copied rows from causing error and close the excel file - it is no longer needed
Next 'closes the for each loop telling it to run through each worksheet copying the NWB worksheet to the MWB sheets
Workbooks(NWB.Name).Activate
Application.DisplayAlerts = False
Workbooks(NWB.Name).Close
Application.DisplayAlerts = True
End If
'increase loopindex (i) by 1
i = i + 1
Loop Until i > LoopMax 'Loop will stop when i hits a blank row in columns A & B
'define the path and filename of the combined workbook
Windows("CombineExcelFiles.xlsm").Activate
Dim savefilepath As String
Dim savefilename As String
savefilepath = Range("E2")
savefilename = Range("F2")
'go back to Master Workbook and Save As with defined path and name
Workbooks(MWB.Name).Activate
ActiveWorkbook.SaveAs filename:=savefilepath & "\" & savefilename
End Sub
