This article will show an approach to move a sheet x times to the right of an active workbook. There are worksheets for revenue, gross margin, EBITDA, operating cash flow and FTEs (full-time employees).

Sure we can manually move a sheet using Excel’s native features. However, we are going to use an
approach using Visual Basic.

How to write the macro

It is recommended to always notate your macro, as it will help you or other developers to review it at a later date. The macro will be called, moveSheetxTimesRight

Firstly, disable the following three Excel properties. Then we want to declare the object variables and their type for the macro. Next, we need to set the location of the right sheet, in relation to the active worksheet.

Given there are only a finite number of sheets to the right of the active sheet. We want to deduce the number of sheets to the right, which will be used as an error handler.

To improve the robustness of the macro, we will insert a number of error handlers that will prevent run-time errors.

The first error handler will force the macro to cease, if the active sheet is located at the far right-hand side of the workbook. As notated above, this code will trigger the input box and the user to specify how many times to move the active worksheet to the right.

This second error handler will exit the user from the macro, if they click “Cancel” on the “InputBox”. The third error handler will manage an instance, where the user enters a non-numerical value into the “InputBox”. It too will stop the macro from running any further.

The final error handler will cope with any inputs, which are false, less than or equal to zero, or values greater than the number of sheets to the right of the active sheet. Otherwise, if the input value is acceptable, we can move to the looping code.

This code will move the active sheet to the right of the “nextSheet”. It will repeat this task multiple times, based on
the input value entered in the “InputBox”.

Once the loop is complete, we can re-enable the three Excel properties. The macro is complete. Let’s review our macro.

Running the macro

We can now test the macro on Sheet1. We want to move it four sheets across. As we can see the macro is working correctly.

Here is the example workbook in the video. It contains the how to move a sheet x times to the right macro.