How to replace a row in Sheet 1 with rows in Sheet 2 and Sheet 3 based on an identifier?

If you want an automated approach using VBA, you can use the following VBA code to replace the row in "Sheet1" with rows from "Sheet2" and "Sheet3":

Open the VBA Editor:
Press ALT + F11 to open the VBA editor in Excel.
Insert a New Module:
Go to Insert > Module to create a new module.
Paste the VBA Code:
Copy and paste the following code into the module:
vba
Copy code
Sub ReplaceRowBasedOnIdentifier()
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim identifier As String
Dim rng As Range, cell As Range
Dim foundCell As Range
Dim lastRow1 As Long, lastRow2 As Long, lastRow3 As Long
Dim i As Long

' Define the worksheets
Set ws1 = ThisWorkbook.Sheets("Sheet1") ' List Orders export
Set ws2 = ThisWorkbook.Sheets("Sheet2")
Set ws3 = ThisWorkbook.Sheets("Sheet3")

' Define the identifier to search for
identifier = "UBS Switzerland AG" ' Change this as needed

' Find and delete the row in Sheet1
lastRow1 = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row
Set rng = ws1.Range("A1:A" & lastRow1).Find(What:=identifier, LookIn:=xlValues, LookAt:=xlWhole)

If Not rng Is Nothing Then
rng.EntireRow.Delete
End If

' Copy rows from Sheet2
lastRow2 = ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row
For i = 1 To lastRow2
If ws2.Cells(i, 1).Value = identifier Then
ws2.Rows(i).Copy Destination:=ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Offset(1, 0)
End If
Next i

' Copy rows from Sheet3
lastRow3 = ws3.Cells(ws3.Rows.Count, 1).End(xlUp).Row
For i = 1 To lastRow3
If ws3.Cells(i, 1).Value = identifier Then
ws3.Rows(i).Copy Destination:=ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Offset(1, 0)
End If
Next i

MsgBox "Rows replaced successfully!"
End Sub
Run the VBA Code:
Close the VBA editor and return to Excel.
Press ALT + F8, select ReplaceRowBasedOnIdentifier, and click Run.