Goal: To insert the same number of specified columns across all worksheets in a workbook.

We are going to present one approach to insert multiple columns, in the same active column across all worksheets in the underlying workbook.

There are ways to select worksheets, and insert a column in the same place. However, what if we would like to insert multiple columns in the same location instantaneously?

This is a follow-up to an earlier video, Insert multiple columns using Excel VBA. The below macro will expand on this code, in order to move across all the worksheets in the active workbook.

Insert columns across worksheets macro

The macro will be called insertColsSheets

Given the looping nature of this macro, it is vital disable the following three Excel properties, which will improve your macro’s speed and efficiency.

We now want to declare the object variables, which will underpin the macro’s performance and ability to execute. Next, we need the macro to remember the name of the current worksheet, because we want the cursor to return to this sheet, once the macro is complete.

It is preferable to set the location of the active column. “ColRef” will reference the active column address. Meanwhile “ColRef2” converts the cell address reference, which will appear in the input box.

The input box will ask the user to specify the number of columns to insert. It is wise to insert an error handler into the macro, as it will prevent run-time errors occurring from incorrect inputs.

We are now ready to compose the loop, which will cycle through all the worksheets and insert the same number of columns. By stating cell A1, it will return the cursor to this address, once the macro has run in each worksheet.

The macro has looped through all the worksheets; we can now return the cursor to the initial worksheet. The macro is complete. We can now re-enable the three Excel properties, which were disabled at the start of the macro.

We can review our macro, before we test it on our worksheets of financial statements for our business units.

Running the macro

We can now run the macro. The macro successfully inserted three columns across each worksheet as we desired.

In summary, here is the example workbook which the video showcased, so as to create the insert columns worksheets macro.

Download Spreadsheet Vault SpreadsheetVault – Insert Columns across Sheets (solution).xlsb