Generate duplicate row whenever a row contains a specific value for a column

[AntiPivotTable]

I have a spreadsheet containing hundreds of row in the following format:

[TABLE="class: s14dydj4-19 lcbBNp"]
<tbody>[TR="class: s14dydj4-20 bDYXIH"]
[TH="class: s14dydj4-25 iHsscd, align: center"]obs_code[/TH]
[TH="class: s14dydj4-25 iHsscd, align: center"]obs_name[/TH]
[TH="class: s14dydj4-25 iHsscd, align: center"]city[/TH]
[TH="class: s14dydj4-25 iHsscd, align: center"]value_yr_1[/TH]
[TH="class: s14dydj4-25 iHsscd, align: center"]value_yr_2[/TH]
[/TR]
[TR="class: s14dydj4-20 bDYXIH"]
[TD="class: s14dydj4-21 VJyPR"]1104125[/TD]
[TD="class: s14dydj4-21 VJyPR"]E and W[/TD]
[TD="class: s14dydj4-21 VJyPR"]ABC[/TD]
[TD="class: s14dydj4-21 VJyPR"]451[/TD]
[TD="class: s14dydj4-21 VJyPR"][/TD]
[/TR]
[TR="class: s14dydj4-20 bDYXIH"]
[TD="class: s14dydj4-21 VJyPR"]1104125[/TD]
[TD="class: s14dydj4-21 VJyPR"]E and W[/TD]
[TD="class: s14dydj4-21 VJyPR"]YZW[/TD]
[TD="class: s14dydj4-21 VJyPR"]26[/TD]
[TD="class: s14dydj4-21 VJyPR"]54[/TD]
[/TR]
[TR="class: s14dydj4-20 bDYXIH"]
[TD="class: s14dydj4-21 VJyPR"]1104126[/TD]
[TD="class: s14dydj4-21 VJyPR"]T and O[/TD]
[TD="class: s14dydj4-21 VJyPR"]YZW[/TD]
[TD="class: s14dydj4-21 VJyPR"]1[/TD]
[TD="class: s14dydj4-21 VJyPR"]251[/TD]
[/TR]
[TR="class: s14dydj4-20 bDYXIH"]
[TD="class: s14dydj4-21 VJyPR"]1104111[/TD]
[TD="class: s14dydj4-21 VJyPR"]G and Y[/TD]
[TD="class: s14dydj4-21 VJyPR"]ABC[/TD]
[TD="class: s14dydj4-21 VJyPR"]54[/TD]
[TD="class: s14dydj4-21 VJyPR"][/TD]
[/TR]
[TR="class: s14dydj4-20 bDYXIH"]
[TD="class: s14dydj4-21 VJyPR"]1104111[/TD]
[TD="class: s14dydj4-21 VJyPR"]G and Y[/TD]
[TD="class: s14dydj4-21 VJyPR"]YZW[/TD]
[TD="class: s14dydj4-21 VJyPR"]12[/TD]
[TD="class: s14dydj4-21 VJyPR"]35[/TD]
[/TR]
[TR="class: s14dydj4-20 bDYXIH"]
[TD="class: s14dydj4-21 VJyPR"]1104111[/TD]
[TD="class: s14dydj4-21 VJyPR"]G and Y[/TD]
[TD="class: s14dydj4-21 VJyPR"]TUS[/TD]
[TD="class: s14dydj4-21 VJyPR"][/TD]
[TD="class: s14dydj4-21 VJyPR"][/TD]
[/TR]
[TR="class: s14dydj4-20 bDYXIH"]
[TD="class: s14dydj4-21 VJyPR"]1104111[/TD]
[TD="class: s14dydj4-21 VJyPR"]G and Y[/TD]
[TD="class: s14dydj4-21 VJyPR"]REW[/TD]
[TD="class: s14dydj4-21 VJyPR"][/TD]
[TD="class: s14dydj4-21 VJyPR"]25[/TD]
[/TR]
[TR="class: s14dydj4-20 bDYXIH"]
[TD="class: s14dydj4-21 VJyPR"]1154111[/TD]
[TD="class: s14dydj4-21 VJyPR"]X and U[/TD]
[TD="class: s14dydj4-21 VJyPR"]TUS[/TD]
[TD="class: s14dydj4-21 VJyPR"]36[/TD]
[TD="class: s14dydj4-21 VJyPR"][/TD]
[/TR]
[TR="class: s14dydj4-20 bDYXIH"]
[TD="class: s14dydj4-21 VJyPR"]1154113[/TD]
[TD="class: s14dydj4-21 VJyPR"]R and S[/TD]
[TD="class: s14dydj4-21 VJyPR"]REW[/TD]
[TD="class: s14dydj4-21 VJyPR"][/TD]
[TD="class: s14dydj4-21 VJyPR"][/TD]
[/TR]
</tbody>[/TABLE]

I would like to duplicate all rows where column 'obs_code' equals 1104111 and have the duplicate contain the same values (or lack of values) and information for columns 'city', 'value_yr_1' and 'value_yr_2'. The only thing that would be different is that the 'obs_code' would now be 1104211 and the 'obs_name' would now be 'Y' (rather than 'G and Y').

How can I achieve this?

It would be great to have the duplicate row created right under the original row.

 

[John_w]

Try this:

Public Sub Insert_New_Rows()

    Dim r As Long
    
    Application.ScreenUpdating = False
    For r = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        If Cells(r, 1).Value = "1104111" Then
            Cells(r, 1).EntireRow.Copy
            Cells(r + 1, 1).Insert shift:=xlDown
            Cells(r + 1, 1).Value = "1104211"
            Cells(r + 1, 2).Value = "Y"
        End If
    Next
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
End Sub