VBA to set an active cell and then insert row/column and copy formats

[smittymj]

Good day all!

This is my first time posting and I'm glad to be here! I'm trying to make a simple excel sheet for people who aren't too familiar with Excel. I'm a total VBA newbie and I would really appreciate help in building a macro to:

1. Find specific text in the sheet and go to that cell/set that cell as active
2. Insert row above that cell and copy format of the row above the new row OR insert column to the left of that cell and copy formats of the column to the left of the new column

Really hoping someone can help me with this. Thank you very much!

 

[smittymj]

Really hoping there's anyone who can help with this!

 

[smittymj]

Hoping to bump this thread

 

[Robby19]

In the developer ribbon you can click "Record Macro." Do exactly what you just posted above and then open up the VBA screen (Alt + F11). Find the macro in the Module and you will now have the bare bones of what you need. Post that using the

[\CODE] tags and someone should be along shortly to provide better assistance.

 

[smittymj]

Hi I managed to get this to work, adding a row:

Sub ADDROW_NR()    Cells.Find(What:="NR1", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

Adding a column is:

Sub ADDCOL()    Cells.Find(What:="ADDCOL", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Selection.EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

Hoping this can help someone in the future!