Add new rows after finding a specific value using vba

Hello everyone,

This is the first time I am posting in here if anyone can help me.

I want to do the below task.

In my excel sheet I want to find a specific text value (eg: Formula 1) then need to add 10 rows above that row(above the cell with Formula 1) and then need to paste some values in an another sheet (in sheet 2, A1:A10). How can I do this using vba mcaro?

Thanks in advance

Hi !

As a starter : just activate the Macro recorder & operate manually then you will get your own free code !

Thank you very much for your suggestion. I have done what you suggested before but the problem is I cant use the record vab again because ( formula 1) can be anywhere in the sheet and the recorded macro will insert rows every time with respect to Rows("11:20") which is related to the early record. This was the recorded macro.

Sub Macro1()

'

' Macro1 Macro

'

'
Cells.Find(What:="Function 1", After:=ActiveCell, LookIn:=xlFormulas, _

LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

MatchCase:=False, SearchFormat:=False).Activate


Rows("11:20").Select

Range("A20").Activate

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Sheets("Sheet2").Select

Range("A14:A23").Select

Selection.Copy

Sheets("Sheet1").Select

Range("A21").Select

ActiveSheet.Paste

End Sub



Any suggestion is kindly appreciated.