I have an output in the form of multiple worksheets.
I have found a solution to merge them all toghether into one workbook. But I would like the sheets to be named after the original file name.
Lets say my file is called 9252400.xlsx, i would then like the corresponding sheet in the workbook to be called 9252400
The code I am using for merging is:
Path = "C:\Documents and Settings\DAJO\My Documents\MD-CPH\PI_Database\0908315-3\PlantInfo_Excel\"
Filename = Dir(Path & "*.xls")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Filename = Dir()
Do one of you have a good idea on how to do that?