Inserting Columns in Excel Worksheet using VBA

Home/Excel VBA/Inserting Columns in Excel Worksheet using VBA

Description:

When we are automating any task we may required inserting columns between other columns or left/right side of a column using Excel VBA.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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 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.

Code:
Sub sbInsertingColumns()
'Inserting a Column at Column B
Range("B1").EntireColumn.Insert
'Inserting 2 Columns from C
Range("C:D").EntireColumn.Insert
End Sub
Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a Module for Insert Menu
  4. Copy the above code and Paste in the code window
  5. Save the file as macro enabled workbook
  6. Press F5 to run it
Input:

Below is the screen shot of the worksheet before running this code.
inserting columns in excel VBA Examples1

Output:

Below is the screen shot of the worksheet after running this code.

inserting columns in excel VBA Example 2

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.

Code:
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
Istructions:

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.

Inserting Columns using Excel VBA

Download – Example File

You can download and explore the use of inserting columns using excel vba.
mongopono.ru – Inserting Columns

LIMITED TIME OFFER
By |March 31st, 2013|Excel VBA|3 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

3 Comments

  1. Aravind September 2, 2015 at 2:12 PM - Reply

    Hi guys,

    I wanted to move and reorganize my data in my excel example: I have 5 columns(A-E) and 100 rows(1-100) in each column in my sheet I want to move the entire column D to the position of the column B and Column B should not be overwritten the entire column B should be moved to the right.
    can anyone teach me how to do it using macro or something else.

    • Abrar May 18, 2017 at 11:32 AM - Reply

      U can just insert a new column beside column B and cut column D and paste it into newly inserted column, that’s it !!

  2. t.maharani January 11, 2016 at 11:56 AM - Reply

    how to create the columns in excel through vb6?
    could to send the coding?

Leave A Comment


Related pages


project tracker excel templatehow to arrange alphabetically in excelcase select vbaexcel remove duplicates from listforgot unprotect sheet passwordaccess vba insertvba isnumberexcel vba querynested if statements in excelexcel vba cells selecthow unprotect excel sheet without passworddelete row vbamessagebox iconaccess vba insertvlookup function in excel with examplemacro to close excel filedata entry form in excel 2007vba delete rowschedule planner excelfinding duplicate cells in excelvba clear entire sheetexcel delete empty rowsvlookup and hlookup tutorial pdfms access vba export query to excelclose workbook vbavb open file dialogvba classesadodb connection stringexcel vba connect to access databasevlookup example downloadsort listbox vbacheckbox in excel vbaexcel vba program examplescost estimating spreadsheethow to arrange alphabetically in excelexcel task management template freehow to write a vlookup formula in excelexcel vba fileformatexcel unhide tabscollection in vbaexcel vba option button valuelookup excel examplesremove password protection excel 2010excel vba learninghow to expand columns in excelnot iserrorms access developer tabcombine multiple excel worksheets into onevba access insert into tabletutorial excel macrosvba text boxexcel templates checklisthow to hide and unhide columns in excel 2010unprotect excel sheet passwordautofit in excel 2013remove blank lines excelrefresh all pivot tablesexcel vba range &3d bubble chart excel 2010vba oledbexcel cell vbahow to find duplicates in a column in excelunhide excel rowmacro remove blank rowsexcel listbox vbaexcel vba match functionvb forms in excellinking excel cellsvlookup tutorial 2013multiple case statement in teradatavba and macrosmacro in excel 2007 tutorial pdfvba dynamic arrayssendmail attachment examplehow to make hlookup in excelexcel vba code if then statementhow to insert multiple columns in excelrept function excelhlook up excelexcel sheet macros