Insert Columns across Workbooks Using VBA In Excel

Welcome this is a follow-up to some earlier videos, which demonstrated how to insert columns under various scenarios:

However in this presentation, we want to effect the insertion of columns into all worksheets, across all open workbooks.

The following workbooks are featured in the below video:

The featured workbooks used to create the macro to insert columns across worksheets and workbooks.

Creating the Insert Columns across Workbooks macro

Remember to go to Visual Basic for Applications, and insert a module. It’s best to narrate your macro, in order for other users of your macro to know its purpose.

We will call this macro, insertColsSheetsWorkbooks

Don’t forget to deactivate the following Excel properties, because it will enhance the macro’s performance and efficiency.

Next, we want to declare our object variables for the macro. It will mean you can change the value of a variable within the code. As well, the macro will continue to use that same variable in the code.

Further, we should state the initial active workbook and worksheet selections. As this will ensure the cursor returns to the initial workbook and worksheet upon completion of the macro.

Finally, we must state the active column from where we want to insert columns.

The iCountCols variable will quantify the number of columns to insert across the worksheets and workbooks.

It is always preferable to add some error handling code in a macro. As it will overcome run-time errors from the incorrect use of the macro by users.

Make sure to write the following piece of code accurately, otherwise the macro may not execute properly.

The Loop feature of the code is vital. As it will allow the macro to shift from open workbook to open workbook. Whilst a second loop is also required, in order to loop through the worksheets for each respective workbook