Merging excel files to form a master data

[Raghavan Sundaram]

Hello All,

I found this macro on the internet that allows me to combine multiple excel reports stored in a folder under one workbook. However, when I use this code now I get a run time error. I am new to macro and would appreciate if someone can help me solve this error. The code that I use below and the line that shows as an error shown in a different font color.


Sub FindExcelFiles()
Dim FileName As String
Dim FolderPath As String
Dim wb As Workbook
FolderPath = "\\CNHKGFS001\users\rsundaram\My Documents\Raghavan\ALL FILES\Customers\RFQ's\Philippines\Kaercher\Inventory 2019-2020\Inventory Reports\"
FileName = Dir(FolderPath & "*.xls")
Do While FileName <> ""
Set wb = Workbooks.Open(FolderPath & FileName)
wb.Sheets(1).Copy after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count).Name = Left(Left(FileName, InStrRev(FileName, ".")), 32)
FileName = Dir()
wb.Close SaveChanges:=False
Loop
End Sub

Thanks!

 

[Fluff]

Hi & welcome to MrExcel.
A sheet name cannot be more than 31 characters in length, so change the 32 to 31

 

[Raghavan Sundaram]

Hello Fluff,

I still get the below error.

 

[Fluff]

You cannot have two sheets with the same name in the same workbook.