I have 10 files and each files contains Sheet1 how can i put these 10 files into one file as 10 sheets.
Please share VBA Code
I have 10 files and each files contains Sheet1 how can i put these 10 files into one file as 10 sheets.
Please share VBA Code
I would probably put this code in a standalone workbook and have it run this macro:
Sub mergeWorkbooks()
Dim files, fn, wb As Workbook
files = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", , "Select Files", "Merge", True)
If TypeName(files) = "Boolean" Then Exit Sub
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb = Workbooks.Add
While wb.Sheets.Count > 1
wb.Sheets(wb.Sheets.Count).Delete
Wend
For Each fn In files
If fn <> ThisWorkbook.FullName Then
With Workbooks.Open(fn)
.Sheets(1).Copy After:=wb.Sheets(wb.Sheets.Count)
'If you want to rename the new sheet to be the filename
'of the sheet it came from uncomment the line below
'wb.Sheets(wb.Sheets.Count).Name = .Name
.Close False
End With
End If
Next fn
wb.Sheets(1).Delete
wb.Activate
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "Done!", vbInformation
End Sub
Display More
Attached an example with that already implemented
Thank You Very Much
Don’t have an account yet? Register yourself now and be a part of our community!