How to consolidate Excel sheets or merge it

Question

I have 70 Excel file and in every excel workbook there are 10 sheets.

But every workbook has SAME Sheets NAME'S like { Excel 1: JAN , Feb , March } & { Excel 2 : Jan , Feb , March } & { Excel 3 : Jan , Feb , March }. The Excel are kept in a single folder.

How to consolidate the Excel by Sheets NAMES. I want to Copy and Paste the data like Sheets JAN to JAN , FEB To FEB , March to March . Using VBA Macro or any other ways to Merge or copy and paste all excel sheets by their name, in a single time.

Answer 1

You can try the below code.

Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As 
Object
Application.ScreenUpdating = False
Workbooks.Open Filename:="D:\Automation Tool\Report - Template.xlsx"
Windows("Report - Template.xlsx").Activate
With ActiveWorkbook
    Worksheets("Lev Report").Activate
End With
Rows("2:" & Rows.Count).ClearContents
Range("A2").Select
Set mergeObj = CreateObject("Scripting.FileSystemObject")

'change folder path of excel files here
Set dirObj = mergeObj.Getfolder("D:\Automation Tool\Leave Report\Leave File")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)

'change "A2" with cell reference of start point for every files here
'for example "B3:IV" to merge all files start from columns B and rows 3
'If you're files using more than IV column, change it to the latest column
'Also change "A" column on "A65536" to the same column as start point
Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
Windows("Leave Report - Template.xlsx").Activate
ActiveWorkbook.Worksheets(2).Activate

'Do not change the following column. It's not the same column as above
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
Call ChangeCaches
End Sub

Answer 2

Before you can run the script you need to setup the configuration (MergeExcel.txt) file.  In Windows Explorer hold shift and right-click on the file you want to merge, select "Copy as path". Paste the path into MergeExcel.txt file.  Each file in the file is the path to the excel file to be merged. The configuration has to reside in the same folder as the VBS script.

c:\folder1\Excel1.xlsx
c:\folder1\Excel2.xlsx
c:\folder3\Excel3.xlsx

Double click to run MergeExcel.vbs.  The script will read MergeExcel.txt file located in the same folder and imports all worksheets into one workbook.  The script is using VBA to open Excel and import worksheets.

Set fso = CreateObject("Scripting.FileSystemObject")
sFolderPath = GetFolderPath()
sFilePath = sFolderPath & "\MergeExcel.txt"

If fso.FileExists(sFilePath) = False Then
  MsgBox "Could not file configuration file: " & sFilePath
  WScript.Quit
End If

Dim oExcel: Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = True
oExcel.DisplayAlerts = false
Set oMasterWorkbook = oExcel.Workbooks.Add()
Set oMasterSheet = oMasterWorkbook.Worksheets("Sheet1")
oMasterSheet.Name = "temp_delete"
oMasterWorkbook.Worksheets("Sheet2").Delete
oMasterWorkbook.Worksheets("Sheet3").Delete

Set oFile = fso.OpenTextFile(sFilePath, 1)   
Do until oFile.AtEndOfStream
  sFilePath = Replace(oFile.ReadLine,"""","")
  
  If fso.FileExists(sFilePath) Then
    Set oWorkBook = oExcel.Workbooks.Open(sFilePath)
    
    For Each oSheet in oWorkBook.Worksheets
      oSheet.Copy oMasterSheet
      'oSht.Move , oSheet
    Next
    
    oWorkBook.Close()
  End If
Loop
oFile.Close

oMasterSheet.Delete
MsgBox "Done"
          
Function GetFolderPath()
    Dim oFile 'As Scripting.File
    Set oFile = fso.GetFile(WScript.ScriptFullName)
    GetFolderPath = oFile.ParentFolder
End Function