Re: Merge multiple Excel files into one file in separate sheets with source file name
Thanks for your help.
The code gives me a error, i think that was the file name extension, maybe to big...
I ´ve made a few modifications. I put the source worksheet name in the destination workbook and it works!
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