How To Add New Columns After Each Subsequent Column Using VBA

In your existing excel worksheet, you might occasionally want to add a new column after each subsequent column. This can be implemented using VBA and I will tell you how to do it in this article.

1. How To Add New Columns After Each Subsequent Column Using VBA.

  1. To add new columns after each subsequent column using VBA, you can use a For loop to loop through each column and insert a new column after it.
  2. For example, the following code will insert a new column after each column in the active worksheet.
    ' loop in each column in the active worksheet.
    ' ActiveSheet refer to the current selected active excel worksheet.
    ' ActiveSheet.Columns.Count return the columns count.
    For i = 1 To ActiveSheet.Columns.Count
        
        ' call the Insert function to insert a new column in the current selected column.
        ActiveSheet.Columns(i).EntireColumn.Insert
    Next i
  3. Below is the entired example.
  4. Open the excel worksheet, then click the Developer tab on the Ribbon.
  5. Click the Visual Basic icon in the Code group to open the Microsoft Visual Basic for Applications window.
  6. Right click the worksheet in the VBAProject pane on the window right side.
  7. Click the item Insert —> Module in the pop up menu list.
  8. Then it will create a new Module file in the VBA project.
  9. Copy & paste the below VBA source code in it.
    Sub InsertNewColumn()
    
    For i = 1 To ActiveSheet.Columns.Count
        ActiveSheet.Columns(i).EntireColumn.Insert
    Next i
    
    End Sub
    
  10. Click the save button to save the code.
  11. Then click the Run button ( a green triangle ) on the top menu bar.
  12. It will open the Macros dialog window.
  13. Select the macro InsertNewColumn from the macros list.
  14. Click the Run button to run it.
  15. Then you can see the result in the active worksheet that you select.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.