VBA help - New row in excel with predefined data

[aka1999]

Hi all,
Is there a way to add a new row with same data everytime I encounter Row ID 5 in the 3rd column per the below screenshot?
I want to add a row with Row ID 6 and Name NewCity.
I have a similar data but with about 100 countries, more columns and have to add particular set of data for all the countries. I am very new to this so that's why asking for a solution for this one if any one can help.
Thank you!

Capture2.jpg

[Paul_Hossler]

Welcome to the forum

Take a minute to read the FAQ t the link in my sig

Try this bit of code and take a look at the attachment. It isn't the most elegant, but should be agood starting place for you

Option Explicit


Sub AddNumber6()
    Dim r As Long, rBottom As Long
    
    Application.ScreenUpdating = False
    
    With ActiveSheet
        rBottom = .Cells(.Rows.Count, 2).End(xlUp).Row
    
        'best to go bottoms up
        For r = rBottom To 2 Step -1
            If .Rows(r).Cells(2).Value = 5 Then
                .Rows(r + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                .Rows(r + 1).Cells(2).Value = 6
                .Rows(r + 1).Cells(3).Value = "New City"
            End If
        Next r
    End With
    
    Application.ScreenUpdating = True


End Sub

[aka1999]

Thank you and it works perfectly in the sample spreadsheet. However, when I try to use it for my spreadsheet and change the details it doesn't work. In my spreadsheet I have to enter data whenever I encounter 14 in the column 32, then a new row after 14 needs to be inserted with 15 in Column 32 and NewCity in Column 36.
I changed few parameters but I am not doing it right and it's insert a row around after 200 rows and also not inserting it correctly.

[Paul_Hossler]

Original:

Is there a way to add a new row with same data everytime I encounter Row ID 5 in the 3rd column per the below screenshot?
I want to add a row with Row ID 6 and Name NewCity.

Revised:

In my spreadsheet I have to enter data whenever I encounter 14 in the column 32,
then a new row after 14 needs to be inserted with 15 in Column 32 and NewCity in Column 36.

Capture.JPG

Option Explicit


Sub AddNumber15()
    Dim r As Long, rBottom As Long
    
    Application.ScreenUpdating = False
    
    With ActiveSheet
        rBottom = .Cells(.Rows.Count, 32).End(xlUp).Row
    
        'best to go bottoms up
        For r = rBottom To 2 Step -1
            If .Rows(r).Cells(32).Value = 14 Then
                .Rows(r + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                .Rows(r + 1).Cells(32).Value = 15
                .Rows(r + 1).Cells(36).Value = "New City"
            End If
        Next r
    End With
    
    Application.ScreenUpdating = True


End Sub

[aka1999]

Thanks a lot Paul, much appreciate it.