Merge if output workbook name already exists

Question

I'm have about 100 .xls files under one folder and I have a Macro script to loop through each one to do some data processing. The objective is to split each workbook into three with name N1, N2, N3 respectively. So far my SplitData Macro worked fine but I have issue with extracted workbooks.

I want to merge newly extracted three workbooks to already existed ones instead of getting alerts like "File N1 already exists." every time. I wonder if there's anyway to achieve this in VBA script? Or any other solutions?

Thanks!

This is my code to loop through folder:

Sub OpenFiles()
    Dim xStrPath As String
    Dim xFileDialog As FileDialog
    Dim xFile As String
    On Error Resume Next
    Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    xFileDialog.AllowMultiSelect = False
    xFileDialog.Title = "Select a folder"
    If xFileDialog.Show = -1 Then
        xStrPath = xFileDialog.SelectedItems(1)
    End If
    If xStrPath = "" Then Exit Sub
    xFile = Dir(xStrPath & "\*.xls")

    Do While xFile <> ""
        Call SplitData
    Loop
End Sub

This is SplitData Macro:

Sub SplitData()
 ' 1. Fill every cells in merged columns for future steps
            Dim cell As Range, joinedCells As Range

            For Each cell In Range("E4:I60")
                If cell.MergeCells Then
                    Set joinedCells = cell.MergeArea
                    cell.MergeCells = False
                    joinedCells.Value = cell.Value
                End If
            Next


            ' 2. Split original sheet into three based on one col value 
            ' loop through selected column to check if has different values
            Const NameCol = "B"
            Const HeaderRow = 3
            Const FirstRow = 4
            Dim SrcSheet As Worksheet
            Dim TrgSheet As Worksheet
            Dim SrcRow As Long
            Dim LastRow As Long
            Dim TrgRow As Long
            Dim Student As String
            Application.ScreenUpdating = False
            Set SrcSheet = ActiveSheet
            LastRow = SrcSheet.Cells(SrcSheet.Rows.Count, NameCol).End(xlUp).Row
            For SrcRow = FirstRow To LastRow
                Student = SrcSheet.Cells(SrcRow, NameCol).Value
                Set TrgSheet = Nothing
                On Error Resume Next
                Set TrgSheet = Worksheets(Student)
                On Error GoTo 0
                If TrgSheet Is Nothing Then
                    Set TrgSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
                    TrgSheet.Name = Student
                    SrcSheet.Rows(HeaderRow).Copy Destination:=TrgSheet.Rows(HeaderRow)
                End If
            TrgRow = TrgSheet.Cells(TrgSheet.Rows.Count, NameCol).End(xlUp).Row + 1
            SrcSheet.Rows(SrcRow).Copy Destination:=TrgSheet.Rows(TrgRow)
            Next SrcRow
            Application.ScreenUpdating = True


            ' 3. Extract three new worksheets into three workbooks 
            Dim Pointer As Long

            Set MainWorkBook = ActiveWorkbook
            Range("E4").Value = MainWorkBook.Sheets.Count

            Application.ScreenUpdating = False   'enhance the performance
            For Pointer = 2 To MainWorkBook.Sheets.Count
                Set NewWorkbook = Workbooks.Add
                MainWorkBook.Sheets(Pointer).Copy After:=NewWorkbook.Sheets(1)
                Application.DisplayAlerts = False
                NewWorkbook.Sheets(1).Delete
                Application.DisplayAlerts = True
                With NewWorkbook
                    .SaveAs Filename:="D:\***\Inventory\" & MainWorkBook.Sheets(Pointer).Name & ".xls"
                End With
                NewWorkbook.Close SaveChanges:=True
            Next Pointer

            Application.ScreenUpdating = True

End Sub

Answer 1

Assuming you have set the filename you want in the path you have in the script, change

Application.DisplayAlerts = True

to

Application.DisplayAlerts = false 

to avoid getting the overwrite warning.

Change it back to true after it's saved to prevent problems elsewhere.

Hope it helps