Goal: To insert the same number of specified rows across all worksheets in a workbook.

This article presents one approach in using a macro to insert multiple rows at the same location across all the worksheets in a workbook. This is a follow-up to an earlier video, “Insert multiple rows using VBA in Excel”.

You can already insert rows simultaneously by grouping sheets as we can do here.

However, if you need to insert a large number of rows. Then it is a bit more unwieldy. In other words, it will require you to scroll down the page, and select the quantity of rows you want to insert.

Hence, the advantage of writing a macro to insert multiple rows in the same location, across all the worksheets in an active workbook.

Insert rows across worksheets macro

Ensure to add “Option Explicit” at the top of the module. It will force us to declare all the relevant variables for a macro. That way if the macro encounters any undeclared variable name, then will trigger an error at compile time.

Notating your macro will always improve everyone’s understanding of your code. Just in case if you or someone else needs to come back at a later date to review it.

The following macro will be named insertRowsSheets. It is prudent to disable the following three Excel properties before running the macro.

Remember to declare the macro’s object variables. As this will ensure the macro runs smoothly, and with no compile errors. Next, we want to state our active row. It will be used to insert the rows in the same place, across all worksheets.

In addition, we must save the location of the active sheet. It is wise to reference “ThisWorkbook” so as to prevent any run-time errors; in the event you have more than one workbook opened, whilst you are running the macro.

The input box will dictate the number of rows, which we will insert across all our worksheets. It is imperative this part of the macro code is accurate. Otherwise, you will get an error prompt in VBA editor, or the macro won’t run correctly.

An error handler is another required element of code. It is advisable because it will help protect the macro from erroneous input values from users. As notated, the loop will enable the macro to insert the same number of rows into each worksheet in the active workbook.

Once the macro has cycled through the sheets, we want to move the cursor back to the initial worksheet in cell address A1. The macro is complete. We can now re-enable those three Excel properties.

Let’s review our macro, before we test it on the worksheets.

Using the macro

We will now run the macro. As we can see, the macro has successfully inserted 6 rows into each worksheet in the active workbook.

Please refer to the following example workbook. It contains the example insert rows across worksheets macro.