How can I make this macro dynamic?

Question

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

hansvogelaar · Answer

&nbsp;
Sub ReplaceUBSRow()
    Dim wsExport As Worksheet
    Dim wsSheet2 As Worksheet
    Dim wsSheet3 As Worksheet
    Dim investorName As String
    Dim foundCell As Range
    Dim foundRow As Long

' Set references to the sheets
    Set wsExport = ThisWorkbook.Sheets("List Orders export")
    Set wsSheet2 = ThisWorkbook.Sheets("Sheet2")
    Set wsSheet3 = ThisWorkbook.Sheets("Sheet3")

' Set the investor name to search for
    investorName = wsSheet2.Range("B2").Value

' Find the row with investor name in the "Export Order List" sheet
    Set foundCell = wsExport.Range("B:B").Find(What:=investorName, _
        LookAt:=xlWhole, MatchCase:=False)

' If the name is found
    If Not foundCell Is Nothing Then
        foundRow = foundCell.Row
        ' Delete the found row
        foundCell.EntireRow.Delete
        ' Copy row 2 from Sheet2 and Sheet3 and insert into Export
        wsSheet2.Rows(2).Copy
        wsExport.Rows(foundRow).Insert
        wsSheet3.Rows(2).Copy
        wsExport.Rows(foundRow).Insert

Application.CutCopyMode = False
    End If
End Sub
aphk12&nbsp;

Forum Discussion

How can I make this macro dynamic?