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.

[e_495]

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. 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: FIND ATTACHED

 

[e_495]

I forgot to mention that the new added row should have the same formatting as the ones above it.