Forum Discussion

aphk12's avatar
aphk12
Copper Contributor
Jun 19, 2024

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

Spoiler
In the attached excel file:

There are 2 sheets created, sheet 2 and sheet 3 with rows of data, and I want to create a macro to replace the original entry in "list orders export" with the two rows from sheet2 and sheet3 respectively -

For ex, in this example excel, UBS Switzerland AG entry in "List Orders export" sheet should be replaced by the two rows, one from Sheet2 and one from Sheet3 based on the name "UBS Switzerland AG"

 

so the original row in the "list order export" sheet should be gone and instead the two rows from sheet 2 and 3 are there to replace it. How can I do this?

Macros and VBA
  • AshaKantaSharma's avatar
    AshaKantaSharma
    Iron Contributor
    Aug 20, 2024
    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.

Resources