How can I replace the original row in “list orders export” with the rows in Sheet 3 and Sheet 4? This should be based on the name / data in the column B, in this example, “UBS Switzerland AG”

i.e. List orders export has row 90 that is UBS entry

The two other numbered sheets have 2 different UBS row entries

I want to delete the original Row 90 and add these 2 new rows from different sheets respectively to the bottom of the “list orders export” sheet

I have this code, but it manually checks for the “UBS xxx” text instead of dynamically using input in Column B…

investorName = “UBS Switzerland AG”
can I set this to a column, for example, column B in “Sheet2”?

so it searches for the name in Column B of Sheet2 in both List orders export AND sheet3, and replaces the entry with that name in list orders export with the 2 entries from sheet2 and sheet3 respectively

Sub ReplaceUBSRow()
Dim wsExport As Worksheet
Dim wsSheet2 As Worksheet
Dim wsSheet3 As Worksheet
Dim investorName As String
Dim lastRow As Long
Dim targetRow As Long
Dim i As Long
Dim foundRow As Long

‘ Set the investor name to search for
investorName = “UBS SWITZERLAND AG”

‘ Set references to the sheets
Set wsExport = ThisWorkbook.Sheets(“List Orders export”)
Set wsSheet2 = ThisWorkbook.Sheets(“Sheet2”)
Set wsSheet3 = ThisWorkbook.Sheets(“Sheet3”)

‘ Find the row with UBS Switzerland AG in the “Export Order List” sheet
lastRow = wsExport.Cells(wsExport.Rows.Count, “B”).End(xlUp).Row
foundRow = 0
For i = 1 To lastRow
If wsExport.Cells(i, 2).Value = investorName Then
foundRow = i
Exit For
End If
Next i

‘ If the row is found
If foundRow <> 0 Then
‘ Delete the found row
wsExport.Rows(foundRow).Delete

‘ Define new rows to insert
Dim newRow1 As Range
Dim newRow2 As Range

Set newRow1 = wsSheet2.Rows(2) ‘ assuming data is in row 2
Set newRow2 = wsSheet3.Rows(2) ‘ assuming data is in row 2

‘ Insert rows from “Sheet2” and “Sheet3” into “Export Order List”
wsExport.Rows(foundRow).Insert Shift:=xlDown
wsExport.Rows(foundRow).Insert Shift:=xlDown

‘ Copy data from “Sheet2” and “Sheet3” to “Export Order List”
newRow1.Copy Destination:=wsExport.Rows(foundRow)
newRow2.Copy Destination:=wsExport.Rows(foundRow + 1)
End If
End Sub

​How can I replace the original row in “list orders export” with the rows in Sheet 3 and Sheet 4? This should be based on the name / data in the column B, in this example, “UBS Switzerland AG” i.e. List orders export has row 90 that is UBS entryThe two other numbered sheets have 2 different UBS row entriesI want to delete the original Row 90 and add these 2 new rows from different sheets respectively to the bottom of the “list orders export” sheet I have this code, but it manually checks for the “UBS xxx” text instead of dynamically using input in Column B…investorName = “UBS Switzerland AG”can I set this to a column, for example, column B in “Sheet2”?so it searches for the name in Column B of Sheet2 in both List orders export AND sheet3, and replaces the entry with that name in list orders export with the 2 entries from sheet2 and sheet3 respectively Sub ReplaceUBSRow()Dim wsExport As WorksheetDim wsSheet2 As WorksheetDim wsSheet3 As WorksheetDim investorName As StringDim lastRow As LongDim targetRow As LongDim i As LongDim foundRow As Long’ Set the investor name to search forinvestorName = “UBS SWITZERLAND AG”‘ Set references to the sheetsSet wsExport = ThisWorkbook.Sheets(“List Orders export”)Set wsSheet2 = ThisWorkbook.Sheets(“Sheet2”)Set wsSheet3 = ThisWorkbook.Sheets(“Sheet3”)’ Find the row with UBS Switzerland AG in the “Export Order List” sheetlastRow = wsExport.Cells(wsExport.Rows.Count, “B”).End(xlUp).RowfoundRow = 0For i = 1 To lastRowIf wsExport.Cells(i, 2).Value = investorName ThenfoundRow = iExit ForEnd IfNext i’ If the row is foundIf foundRow <> 0 Then’ Delete the found rowwsExport.Rows(foundRow).Delete’ Define new rows to insertDim newRow1 As RangeDim newRow2 As RangeSet newRow1 = wsSheet2.Rows(2) ‘ assuming data is in row 2Set newRow2 = wsSheet3.Rows(2) ‘ assuming data is in row 2′ Insert rows from “Sheet2” and “Sheet3” into “Export Order List”wsExport.Rows(foundRow).Insert Shift:=xlDownwsExport.Rows(foundRow).Insert Shift:=xlDown’ Copy data from “Sheet2” and “Sheet3” to “Export Order List”newRow1.Copy Destination:=wsExport.Rows(foundRow)newRow2.Copy Destination:=wsExport.Rows(foundRow + 1)End IfEnd Sub  Read More

​