When we are automating any task we may required inserting columns between other columns or left/right side of a column using Excel VBA.
For example, we may generate employee performance report based on their tenure in an organization. The first column would be ‘Employee ID’ and the last column would be ‘Tenure (in months)’. Between these two columns I want Employee performance for each month. We can automate this and we need to add the number of column between these existing tow columns based on the tenure of the particular employee. Since one employee may working from last 2 years, some one lease may be joined 2 months back.
So, while generating the reports for each employee, we need to add the number of columns as per their tenure. We will see this practically in the below example.
- Inserting Columns in Worksheet using Excel VBA – An Example
- Inserting Columns in Worksheet using Excel VBA – Case study
- Inserting Columns in Worksheet using Excel VBA – Download Example Files
Inserting Columns in Excel – Solution(s):
We can use EntireColumn.Insert method to insert a column in worksheet using Excel VBA.
Inserting Columns in Worksheet using Excel VBA – An Example
The following example will show how to insert columns in excel worksheets. In this example I am inserting a column at B and inserting multiple columns at C and D.
Sub sbInsertingColumns() 'Inserting a Column at Column B Range("B1").EntireColumn.Insert 'Inserting 2 Columns from C Range("C:D").EntireColumn.Insert End Sub
- Open an excel workbook
- Press Alt+F11 to open VBA Editor
- Insert a Module for Insert Menu
- Copy the above code and Paste in the code window
- Save the file as macro enabled workbook
- Press F5 to run it
Below is the screen shot of the worksheet before running this code.
Below is the screen shot of the worksheet after running this code.
Inserting Columns in Worksheet using Excel VBA – Case study
As discussed above here is the simple example to generate the employee performance report using Excel VBA based on their tenure. This example will insert the number of columns based on the tenure in months.
Sub sbInsertingColumnsCaseStudy() 'Declaration Dim iCntr, jCntr 'Setting the active sheet to an obect for future reference Set shtSource = ThisWorkbook.ActiveSheet 'Adding a workbook Set wb = Workbooks.Add For iCntr = 2 To 10 ' for each employee 'Adding worksheet for each employee Set sht = wb.Worksheets.Add sht.Name = shtSource.Cells(iCntr, 1) 'Printing labels and employee ID and tenure sht.Cells(1, 1) = "Employee ID" sht.Cells(1, 2) = "Tenure" sht.Cells(2, 1) = shtSource.Cells(iCntr, 1) sht.Cells(2, 2) = shtSource.Cells(iCntr, 2) 'Pinting monts as per employee tenure. For jCntr = 1 To shtSource.Cells(iCntr, 2) sht.Range("B1").EntireColumn.Insert sht.Range("B1") = Format((Now() - jCntr * 30), "mm-yyyy") Next Next End Sub
Download the example workbook and click on the ‘Generate Employee Performance Report’ button. It will create new workbook and worksheets as report for each employee.
Download – Example File
You can download and explore the use of inserting columns using excel vba.
ANALYSISTABS – Inserting Columns