Welcome, I want to show you one approach in
using a macro to insert multiple rows at the
same location across all the worksheets in a workbook.
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 and will require
require you to scroll down the page and then
select the quantity of rows you want to insert.
Go into to "Visual Basic for Applications", and insert a module.
"Option Explicit" will force us to declare
all the relevant variables for a macro;
any undeclared variable name will trigger an error at compile time.
Notating your macro will always improve
everyone's understanding of your code,
if you or someone else needs to come back
at a later date to review it.
We will call the macro "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,
which we will use to insert the rows in the same
place across all worksheets.
We also want to 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, 0:04:38.066,0:04:38.033
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 element of code,
which is advisable because it will help to 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.
We will now run the macro.
As we can see, the macro has
successfully inserted 6 rows
into each worksheet in the active workbook.