Hello, we will present one way to copy selected
worksheets, multiple times in a workbook.
We have three sheets for an income statement,
balance sheet and forecast.
Sure we can manually copy the selected
sheets once using Excel's native features.
However, we will apply an approach using
Visual Basic, in order to copy these selected
sheets multiple times.
Go to Visual Basic for Applications,
and insert a Module.
Narrating your macro is always wise, as it will
help you or others to understand the code at a later date.
We will call the macro, copyMultiSheets
We need to disable the following three Excel
properties, before the macro runs.
Next, we need to declare the object
variables and their type for the macro.
We now want to set the location of the
first selected sheet.
The input box will ask the user to state
how many times to copy the selected sheets.
To improve the robustness of the macro,
we will add a number of error handlers
that will overcome run-time errors.
The first error handler will exit the user from the
macro, if they click Cancel on the InputBox.
The second error handler will exit the user
from the macro, where the user enters a
non-numerical value into the InputBox.
The final error handler will deal with any inputs,
which are false, or less than or equal to zero.
If the input value is good,
we can move to the looping code.
This line of code will loop through all sheets
in the selected sheets.
Whilst this code will copy the active sheet,
and place the copy before the active sheet.
The y loop will repeat this task multiple times,
based on the value entered in the InputBox.
However, we want to ensure the original sheet
copied multiple times,
is placed in front of its copied versions.
Hence the z loop,
will move the original sheet
before the previous sheet.
It will repeat this task multiple times,
as per the number entered in the InputBox.
The code will loop to the next worksheet
in the selected sheets called sh,
and it will repeat the above loops again.
Once the looping is complete, we want to
return the cursor to the first selected sheet.
The macro is complete,
we can re-enable the three Excel properties.
Let's review our macro.
We can now test the macro on the three sheets.
We want to copy the selected sheets three times.
As we can see the macro is working correctly.