Cannot merge Excel files with same worksheet name

Question

I am quite new to VB coding and am attempting to merge Excel spreadsheets then combine the data into one spreadsheet. I found this code that worked perfectly until I encountered files that had the same worksheet name.

Sub MergeExcelFiles()
Dim fnameList, fnameCurFile As Variant
Dim countFiles, countSheets As Integer
Dim wksCurSheet As Worksheet
Dim wbkCurBook, wbkSrcBook As Workbook

fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)

If (vbBoolean <> VarType(fnameList)) Then

    If (UBound(fnameList) > 0) Then
        countFiles = 0
        countSheets = 0

        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual

        Set wbkCurBook = ActiveWorkbook

        For Each fnameCurFile In fnameList
            countFiles = countFiles + 1

            Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)

            For Each wksCurSheet In wbkSrcBook.Sheets
                countSheets = countSheets + 1
                wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
            Next

            wbkSrcBook.Close SaveChanges:=False

        Next

        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic

        MsgBox "Processed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files"
    End If

Else
    MsgBox "No files selected", Title:="Merge Excel files"
End If
End Sub

When running this code I get a

run-time error '1004': Cannot rename a sheet to the same as another sheet, a referenced object library or a workbook referenced by Visual Basic.

I would like assistance in renaming the worksheets as they get imported.

Marius Katinas Marius Katinas · Accepted Answer · 2019-03-27T07:58:13

In Excel2016 this issue cannot be re-created, as Excel automatically adds "(1)", "(2)"... to the name of a sheet that has a same name as copied sheet.

If you are using an older version of Excel, solving this issue would require to re-create this behavior.

To rename a sheet I'm referencing this answer: Excel rename sheet with if sheet name already exists

Create a new function to check worksheet names in both workbooks:

Private Function VerifySheetName(ByVal sourceWorkbook As Workbook, ByVal targetWorkbook As Workbook, ByVal sheetName As String) As String
Dim combinedSheets As New Collection
Dim tempSheet As Worksheet

For Each tempSheet In sourceWorkbook.Sheets
    combinedSheets.Add tempSheet
Next tempSheet

For Each tempSheet In targetWorkbook.Sheets
    combinedSheets.Add tempSheet
Next tempSheet

For Each tempSheet In combinedSheets
    If tempSheet.Name = sheetName Then
        VerifySheetName = sheetName & "_" & combinedSheets.Count
    End If
Next tempSheet

End Function

Call this function in your loop:

For Each wksCurSheet In wbkSrcBook.Sheets
tempSheetName = VerifySheetName(wbkSrcBook, wbkCurBook, wksCurSheet.Name)
If Not wksCurSheet.Name = tempSheetName Then
    wksCurSheet.Name = tempSheetName
End If

countSheets = countSheets + 1
wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
Next

And don't forget to add new variable declaration if you're using Option Explicit (you should!)

Dim tempSheetName As String

P.S. When initiating multiple variables in the same line, if you omit the variable type, by default it's cast as variant:

Dim countFiles, countSheets As Integer

In this case, countFiles is of variant type ans countSheets is of integer type. If you need explicit variably type, you need to assign type to each variable:

Dim countFiles as Integer, countSheets As Integer

Cannot merge Excel files with same worksheet name

1 Answers