This tutorial will outline an approach to create a macro, which will enable users to insert rows across workbooks.

The following presentation is a follow-up to earlier videos on inserting rows:

• Insert multiple rows using VBA in Excel
• Excel insert multiple rows & fill down
• Insert multiple rows across sheets using VBA in Excel

We will base the following video on the following data sets.

Creating the insert rows across workbooks macro

Go to Visual Basic for Applications, and insert a module. Whenever writing a customized macro in Excel, it’s advisable to notate your macro. As it will help future users of your macro will understand its purpose.

Let’s call this macro, insertRowSheetsWorkbooks

There are a few important best practices with composing your own macro in Excel:

• Disabling Excel properties. As it will improve the macro’s performance and efficiency.
• Declaring object variables. This will mean you can change the value of a variable within the code. As well as continue to use that variable in the code.
• Error handling code. It will prevent run-time errors caused by the erroneous use of the macro by users.

The iCountRows variable will quantify the number of rows to insert across the worksheets and workbooks. The macro contains two loops.

The first loop will enable the macro to move from open workbook to open workbook. Whilst the second loop will cycle through the worksheets for each respective workbook.

Using the macro

Make sure to write the entire code accurately, because otherwise the macro may not execute properly. Remember to review the macro one final time. Then it is time to trial the macro in our open Excel application.

In summary, here are the example workbooks which the video showcased, in order to compose the macro.