This video will provide you an approach to group rows across multiple worksheets in a workbook.
We have a financial model with numerous worksheets
for different business units and their financials.
One current drawback with Excel is the inability
to group the same rows simultaneously across multiple worksheets.
We will compose a macro to enable you to group
the same selected rows across worksheets.
Let's insert a module into the workbook,
and we will call the module "Rows".
We will call this multiple row group selection macro, groupRows
We will now disable the key Excel properties of ScreenUpdating,
EnableEvents and Calculation,
which will greatly enhance the speed and efficiency of the macro's performance.
Error handling will help to overcome potential problems the macro encounters.
It will permit the macro to bypass problems
in a worksheet and move to the next worksheet.
It is time to now declare the relevant variables.
The code will identify the selected row range,
which we want to apply the grouping of rows across the worksheets.
We can now loop through the visible or unhidden worksheets,
excluding the "Index" worksheet because it doesn't contain financial information,
which we want to group up.
It is crucial to stipulate each worksheet
with the Activate property, in order to successfully
group the rows based on the specified selection.
This line of code will group the selected rows in each worksheet
Whilst this line will hide the grouped rows in each worksheet.
As denoted, this code will move the cursor in each worksheet to cell A1.
The Next will move the macro to the next visible worksheet.
Once the macro has looped through
all the visible worksheets, the cursor will default
back to the "Index" worksheet and to cell address A1.
As the macro is now finished,
we can re-enable the Excel properties.
In summary,
here is the complete code to group selected rows
across multiple, visible worksheets.
We are now ready to test the macro across worksheets.
Stay tuned for a follow up video,
which will show you how to ungroup and unhide
grouped rows across worksheets simultaneously.