Inserting Columns in CSV/Excel

tomsh · Mar 25th 2016

Is there a way i can insert a column automatically when i open the csv/excel file.

I tried doing the way i know, i inserted a button on the sheet and onclick of that button i wrote a simple code that insert the columns by clicking on button.

But, I dont know how to give the column Headings automatically and the values to those column will always be 1,2, 3, 4, 5.

Say for example i need to add 5 columns in the existing grid with the Heading as "HE01" , "HE02" , "HE03", "HE04" and "HE05".

Is there a way i can do this task without having a button onclick. I mean whenever i open that file it should create 5 extra columns with the values on it.

any help on this would be great.

Thanks
TOm

**Ger Plante** · Mar 26th 2016

Re: Inserting Columns in CSV/Excel

Hi Tom - you didnt really give a whole lot of detail, but basically if you want to do something when the workbook opens, then you need to put your code into the workbook_open event which triggers ever time the workbook is opened. For example....

Code

Private Sub Workbook_Open()


ActiveSheet.Range("A1:E1").Insert shift:=xlToRight
ActiveSheet.Range("A1:E1").Value = Array("He01", "He02", "He03", "He04", "He05")


End Sub

This inserts 5 columns from A1 to E1 and then populates it with the necessary values.

Ger

tomsh · Mar 28th 2016

Re: Inserting Columns in CSV/Excel

Ger,

Thank you so much for the reply. I am sorry for not explaining the question in detail. I trying to insert the columns in between the existing ones. Say for example i have headings and their values for the corressponding columns from Column A to column H, and am trying to insert extra columns in between (A-H) thats starts from Column C toG i am trying to add Houly (HE1 - HE05) and the data for HE1-05 would 1,2,3,4&5. Attached the same sheet.

**Ger Plante** · Mar 28th 2016

Re: Inserting Columns in CSV/Excel

OK, got it... slight change to your code...

Code

Private Sub Insert_Columns()
     
    ActiveSheet.Range("C:G").Insert shift:=xlToRight
    ActiveSheet.Range("C1:G1").Value = Array("He01", "He02", "He03", "He04", "He05")
     
End Sub

Dont call the macro "Workbook_open" as this is a predefined keyword.

I was simply saying that if you want to use the above macro, then you need to add this macro to the workbook open event. This means the macro will run EVERY time you open the workbook. You may not be clear on how to do that, so take a read of this:

http://www.ozgrid.com/VBA/auto-run-macros.htm

Just be careful, because if you run this EVERY time the workbook opens, then it will always add in 5 columns every time, which you probably dont want. So I will let you think about that, and how you might solve it. If you need help with it, then start a new thread... we're here to help, but have a think about it yourself first

Ger

Inserting Columns in CSV/Excel

Participate now!

Share