Нужно добавить код в макрос, чтобы после вставки новых данных с названием листов, она удаляла старые на Лист6
Sub MergeSheetsContainingAshan4()
Dim SourceWorkbook As Workbook
Dim SourceWorksheet As Worksheet
Dim TargetWorkbook As Workbook
Dim TargetWorksheet As Worksheet
Dim LastRow As Long
Dim NextRow As Long
Dim ExistingSheetRow As Long
Dim SheetExists As Boolean
Dim ContentMatch As Boolean

' Открываем рабочую книгу-источник по ссылке в ячейке A1 на листе5
Set TargetWorkbook = ThisWorkbook
Set SourceWorkbook = Workbooks.Open(TargetWorkbook.Worksheets("Лист5").Range("A1").Value)

' Создаем новый лист “Лист6” в целевой рабочей книге
On Error Resume Next ' Если лист с таким именем уже существует, переходим к следующей строке
Set TargetWorksheet = TargetWorkbook.Worksheets("Лист6")
If TargetWorksheet Is Nothing Then
Set TargetWorksheet = TargetWorkbook.Worksheets.Add(After:=TargetWorkbook.Worksheets(TargetWorkbook.Worksheets.Count))
TargetWorksheet.Name = "Лист6"
End If
On Error GoTo 0

' Цикл копирования данных
For Each SourceWorksheet In SourceWorkbook.Worksheets
If InStr(1, SourceWorksheet.Name, "ашан", vbTextCompare) > 0 Then
' Проверяем наличие совпадений имени листа и содержимого
SheetExists = False
ContentMatch = False
For ExistingSheetRow = 1 To TargetWorksheet.Cells(TargetWorksheet.Rows.Count, 1).End(xlUp).Row
If TargetWorksheet.Cells(ExistingSheetRow, 1).Value = SourceWorksheet.Name Then
SheetExists = True
' Сравниваем содержимое столбцов
ContentMatch = True
For col = 1 To SourceWorksheet.UsedRange.Columns.Count
If SourceWorksheet.Cells(1, col).Value <> TargetWorksheet.Cells(ExistingSheetRow, col + 1).Value Then
ContentMatch = False
Exit For
End If
Next col

        If ContentMatch Then Exit For
    End If
Next ExistingSheetRow

' Если имя листа и содержимое не совпадает, копируем данные
If Not SheetExists Or Not ContentMatch Then
    ' Находим следующую пустую строку в столбце B на “Лист6”
    LastRow = TargetWorksheet.Cells(TargetWorksheet.Rows.Count, 2).End(xlUp).Row + 1
    
    ' Копируем данные из найденного листа на “Лист6”
    SourceWorksheet.UsedRange.Copy TargetWorksheet.Cells(LastRow, 2)
    
    ' Записываем имя листа в столбец A на той же строке, где были скопированы данные
    NextRow = TargetWorksheet.Cells(TargetWorksheet.Rows.Count, 2).End(xlUp).Row + 1
    
    With TargetWorksheet.Range("A" & LastRow & ":A" & NextRow - 1)
        .Value = SourceWorksheet.Name
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
End If
End If
Next SourceWorksheet

' Закрываем рабочую книгу-источник
SourceWorkbook.Close SaveChanges:=False
' Delete old sheets in "Лист6"
Dim i As Long
Dim SheetName As String
Dim SheetFound As Boolean

Application.DisplayAlerts = False ' Disable alert messages

For i = TargetWorksheet.Cells(TargetWorksheet.Rows.Count, 1).End(xlUp).Row To 2 Step -1 ' Loop backwards to avoid issues with deleting rows
SheetName = TargetWorksheet.Cells(i, 1).Value
SheetFound = False
For Each SourceWorksheet In SourceWorkbook.Worksheets
If SourceWorksheet.Name = SheetName Then
SheetFound = True
Exit For
End If
Next SourceWorksheet
If Not SheetFound Then
TargetWorkbook.Worksheets(SheetName).Delete
TargetWorksheet.Rows(i).Delete
End If
Next i

Application.DisplayAlerts = True ' Enable alert messages

End Sub