Macro to find specific text in a cell and then create multiple rows above

Can anyone help me to create a macro that finds a specific text string in the sheet and then creates 43 new rows above it? the text string only occurs once on the sheet. Additionally, i need each of the 43 newly created rows to to have "1" entered in cell A. The text is to find "Building Report 2"

The idea is to be able add a new page to the first building report (above). I will copy the macro to each of 5 building report pages, changing the searched string to "Building Report 3", "4", etc. and link them to buttons on each report. Basically a separate identical macro in each one-page-long report that will add a blank page to that specific building report each time the user clicks the button at the bottom of the page.

Please Login or Register  to view this content.

if problem attach a sample file

In which column would you find the text "Building report 2" ?

Also, do you want the macro to do the row insertion for building report 3,4,5,etc?

Originally Posted by Willardio

Thanks Patel, but it doesn't seem to do anything.

attach a sample file

Hi, Willardio,

... finds a specific text string in the sheet

Maybe like this on one sheet:

Please Login or Register  to view this content.

Ciao,
Holger

Thank you Holger, it works great... are you frequently called a genius?!

I modified your code to insert the rows below the report after realizing it makes more sense with my filters. The only problem now is that the "1" does not appear in cell A of each new row. I need the 1's to protect the rows from my filter (not included in the sample) --if there another way to lock the rows from being filtered I'd be very interested in knowing that as well.

Here is a sample of my sheet: br sample for adding a page.xlsm

Here are my modifications to your code and the error message I am getting:

Please Login or Register  to view this content.

Thanks so much for your help so far!

~Willardio~

"Building Report 2" appears in column B. I would rather have a seperate macro for each report because ease of use is essential. I plan to copy the code to a new macro for each report and link it to botton at the bottom of the page. Holger's code (see below) is good, only thing is that I don't get my "1" in cell A of each new line. the "1" is necessary to protect the rows from my filter.

Here is a sample of my sheet: br sample for adding a page.xlsm

Here is Holger's code with my modifications:

Please Login or Register  to view this content.

Hi, Willardio,

try it with this modified code which worked fine on your copy of the workbook:

Please Login or Register  to view this content.

Ciao,
Holger