Adding a row above a cell with a specific character string (to add row to bottom of table) using VBA code in excel and a macros button.

Question

Hi, I have seen a few thread on this, however none have worked specifically for what I am trying to do.

I am create a template for a form that users will fill out in excel. The form is divided in sections and a few of these sections require an "Add New Row" button. I have the button set up and I am trying to program the code to add a new row at the end of the section. This new row should have the same formatting as the rows above. To do this I am referencing the cell below that has a specific text string in the cell, so that the new row is added above that cell. I have the following VBA code as of now. However, when I try to run the code by clicking the button, I get an error at the 8th line. I have posted a picture with the line highlighted. I am a beginner at all of this and am still learning VBA code. Is anyone able to help me with this please?

Thank you!

CODE:

Private Sub CommandButton1_Click()

Dim mySheets

Dim i As Long 

Dim NewRow As Long 

Const CharString As String = "Development Goals (Training, Career Growth)" 

mySheets = Array("Career Plan 2022") 

For i = LBound(mySheets) To UBound(mySheets) 

    If mySheets(i).Value = CharString Then 

        NewRow = mySheets(i).Row 

        With Sheets(mySheets(i)) 

            .Range("Ai").EntireRow.Insert shift:=xlDown 

            Rows("i-1:i-1").Copy Range("i") 

            Range("i:i").ClearContents 

        End With 

    End If 

Exit For 

Next i

End Sub

PICTURE:

Answer 1

How about

Private Sub CommandButton1_Click()
    Dim wsh As Worksheet
    Dim rng As Range
    Dim NewRow As Long
    Const CharString As String = "Development Goals (Training, Career Growth)"

    Set wsh = Worksheets("Career Plan 2022")
    Set rng = wsh.Range("A:A").Find(What:=CharString, LookAt:=xlWhole)
    If Not rng Is Nothing Then
        rng.EntireRow.Insert
        rng.Offset(-2).EntireRow.Copy
        rng.Offset(-1).EntireRow.PasteSpecial Paste:=xlPasteFormats
        Application.CutCopyMode = False
    End If
End Sub

Answer 2

It's not clear how mySheets = Array("Career Plan 2022") would set a range. What is "Career Plan 2022"?

Answer 3

"Career Plan 2022" is the name of the sheet that the form is in.

Answer 4

Perhaps this. Please test on a copy of your workbook.

Private Sub CommandButton1_Click()
    Dim wsh As Worksheet
    Dim rng As Range
    Dim NewRow As Long
    Const CharString As String = "Development Goals (Training, Career Growth)"

    Set wsh = Worksheets("Career Plan 2022")
    Set rng = wsh.Range("A:A").Find(What:=CharString, LookAt:=xlWhole)
    If Not rng Is Nothing Then
        rng.EntireRow.Insert
        rng.Offset(-2).Resize(2).EntireRow.FillDown
    End If
End Sub

Answer 5

Wow, I would have never thought to do this. Thank you, it worked.

How could I possibly clear the contents of the new row such that it is an empty row with the same formatting?? When I add .ClearContents to this line

rng.Offset(-2).Resize(2).EntireRow.FillDown

It unmerges the cells, which makes sense because I am typing ClearContents. is there another command/function that can do this?