Insert A New Row Based on a Criteria linked with button

[Jafir]

Hi all,

I've been searching a solution to my issue on the board but could not find a working solution that covers my needs.
I'd really appreciate some help to come up with a VBA code that does the following:
I have two sheets in a workbook like below;

In sheet2 I have a cell B contains some value like below;

Once I press the Button 3, a new row (based on criteria B2 above) should be added in Sheet1 (below 4) like below.

Thanks for your help

 

[Bosquedeguate]

Try this code for your button

Private Sub btnAddRow_Click()
  Dim wks As Worksheet
  Dim LR As Long
  Dim siteID
  Dim siteIDFound As Boolean
  Set wks = ActiveSheet
  
  LR = wks.Range("A" & Rows.Count).End(xlUp).Row
  
  siteID = wks.Range("$B$1")
  siteIDFound = False
  For r = 2 To LR
    If wks.Cells(r, 1) = siteID And Not siteIDFound Then
      siteIDFound = True
    End If
    
    If siteIDFound And wks.Cells(r, 1) <> siteID Then
      wks.Range("A" & r).Insert xlShiftDown
      Exit For
    End If
  Next r
  
  If Not siteIDFound Then
    MsgBox "Site ID: " & siteID & " not found."
  End If
  
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

Hard to see for sure but it looks like that ID number in Sheet2 is in cell B2
Based on that, try attaching this macro to Button 3

Sub Insert_Row()
  On Error Resume Next
  Sheets("Sheet1").Columns("A").Find(What:=Range("B2").Value, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious).Offset(1).EntireRow.Insert
End Sub

 

[Jafir]

Welcome to the MrExcel board!

Hard to see for sure but it looks like that ID number in Sheet2 is in cell B2
Based on that, try attaching this macro to Button 3

Sub Insert_Row()
  On Error Resume Next
  Sheets("Sheet1").Columns("A").Find(What:=Range("B2").Value, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious).Offset(1).EntireRow.Insert
End Sub

It works perfectly. However, there is another challenge that Newly created rows are not possible to remove.

 

[Peter_SSs]

It works perfectly.

Good news!

However, there is another challenge that Newly created rows are not possible to remove.

I did not have any trouble removing the newly created row. How did you try to do it?

 

[Jafir]

Good news!


I did not have any trouble removing the newly created row. How did you try to do it?

 

[Jafir]

Good news!


I did not have any trouble removing the newly created row. How did you try to do it?

Issue is resolved, Thanks Peter_SSs

​

​

 

[Peter_SSs]

Issue is resolved, Thanks Peter_SSs

Glad to hear it! Thanks for letting us know.