Insert Rows after captured Data

Hi

I am a newbie and need help with creating a macro which will help me create a template in Excel that always has 5000 rows and has "CHECK" on the 5001 row.

Example

Currently i have 20 rows of data, i need to add an additional 4980 blank rows underneath to give me a total of 5000 and i then need to have "CHECK" on row 5001. The number of rows with data can vary, so one month it may be 20 and the next 30, but regardless of how many rows of data i have, i always need to have 5000 rows(which includes the rows with data) and "CHECK" on row 5001

Thank you in advance

How should your template look like? In which column do you need the "CHECK" ? Should it create a new empty sheet or worksheet?
you could try:

Please Login or Register  to view this content.

Insert this code in a Module and run it from any sheet.

Hi

Thank you for your response. To clarify i have attached a sample template. From B15 i need 5000 rows and the "CHECK" needs to be on the 5001 row. The 5000 rows should include the 24 rows of data which on spreadsheet are from B15:B38.

I also need to ensure that the 'Prepared By', 'Authorised by', 'Input By', and 'Date' blocks, always show after the 5001 row, so basically i need to keep the template as is but have 5000 rows from B15 downwards.

Hope that makes sense

Regards

Hi zahus,

Attached 2 procedures.
The first will only add the 5000 rows to the template.
The second one, will copy the current sheet into a new sheet and in that new sheet, it will expand the 5000 rows, leaving the original sheet as is.

Hope it does the trick.


Importante:
-I moved your code to a module. You should allways put your procedures in modules and name the modules with a name that describes what's inside. It will make your life easier when your codes grows big.

-It is also important that you indent your codes. It will make it much much easier to read and understand.