How to merge multiple Excel files into one file in separate sheets with source file name
Category: [Excel] Demo Available
How to merge multiple Excel files into one file in separate sheets with source file name
Requirement:
The user uses this code to merge multiple excel files (*.xlsx) to one master file in a different worksheets with the name sheet1, sheet2, sheet3,...
The user wants to put the destination sheet name with the source excel file name.
Code:
Public Sub test()
'// Untested as written/edited freehand, but the basic idea is there.
Dim myFile As String, sh As Worksheet, myRange As Range
Const myPath = "C:\Users\" ' to be modified
Workbooks.Add 1 ' Add a new workbook
myFile = Dir(myPath & "*.xlsx")
Do While myFile <> ""
Set sh = ActiveWorkbook.Sheets.Add()
Workbooks.Open myPath & myFile
Cells.Copy Destination:=sh.Range("A1")
' Set myRange = ActiveSheet.UsedRange
' Set myRange = myRange.Offset(1).Resize(myRange.Rows.Count - 1)
' myRange.Copy sh.Range("A65000").End(xlUp).Offset(1)
ActiveSheet.Name = myFile
Workbooks(myFile).Close False
myFile = Dir
Loop
End Sub
Solution:
Code:
Public Test_()
Dim myFile As String, sh As Worksheet, myRange As Range
Dim myPath As String
myPath = InputBox("input the xlsx files path with a \ in the end ")
myFile = Dir(myPath & "*.xlsx")
Dim sh_name As String
Do While myFile <> ""
Set sh = ActiveWorkbook.Sheets.Add()
Workbooks.Open myPath & myFile
sh_name = Workbooks(myFile).ActiveSheet.Name
Cells.Copy destination:=sh.Range("A1")
sh.Name = sh_name
Workbooks(myFile).Close False
myFile = Dir
Loop
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by patel and JCHULA.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to count worksheets |
| How to select cell A1 on all sheets prior to closing the workbook |
| How to create VBA to copy specific column from one sheet to another |
| How to use a macro to copy rows from multiple worksheets based on a cell value greater than zero |
| How to consolidate data into single sheet from the selected spreadsheets |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.