Insert Column after Last Inserted Column

A simple macro:

1.Run macro: Add a column after A (I have data at column B)
-->Now column A:same, Column B: new blank, column C:previous column B
2.Run macro again: Add a column after B
...
3.Run macro again: Add a column after C
...

Hi, mariosmk555,

maybe like this:

Sub EF1008505()
Cells(1, Columns.Count).End(xlToLeft).EntireColumn.Insert   'adjust the row number to match data
End Sub

or

Sub EF1008505_2()
With ActiveSheet.UsedRange
  .Cells(.Cells.Count).EntireColumn.Insert
End With
End Sub

Ciao,
Holger

Hi, mariosmk555,

no need for a full quote here according to Forum Rule#12.

Only if Data exceeds Column C it would be of use here:

Sub EF1008505_3()
Dim lngIns As Long
lngIns = Cells(1, Columns.Count).End(xlToLeft).Column
If lngIns < 3 Then lngIns = 3
Cells(1, lngIns).EntireColumn.Insert   'adjust the row number to match data
End Sub

Starting at a certain range will not insert new columns prior to any filled cells, that´s a different requirement.

Ciao,
Holger

Hi, mariosmk555,

you should be more precise in what you are asking. In the opening post it was one column (the last one) which should be shifted. Now it´s two columns, and I´m not so sure about the columns you have used or the range where to insert:

Sub EF1008505_4()
'insert two columns before the last two columns of data
Dim lngIns As Long
lngIns = Cells(1, Columns.Count).End(xlToLeft).Column - 1
If lngIns < 3 Then lngIns = 3
Cells(1, lngIns).Resize(1, 2).EntireColumn.Insert
End Sub

Sub EF1008505_5()
'will start at Column C and add two columns when the workbook isn´t closed in the meantime
'If you wan to keep the value for the next opening you could write it to a cell on a hidden sheet,
'use a Name to store it or store the value in a textfile.
Static lngIns As Long
If lngIns = 0 Then
  lngIns = 3
Else
  lngIns = lngIns + 2
End If
Cells(1, lngIns).Resize(1, 2).EntireColumn.Insert
End Sub

Ciao,
Holger

You may try this code also.....
The code will ask you to input the column of interest, so you need to input the column alphabet. Suppose you want to insert two column after the col. D, in that case you need to input either d or D in the input box.

Sub InsertColumn()
Dim c As String
'You need to input the column alphabet like d,e,f,g...etc
c = InputBox("After which column you want to insert new column?")
Cells(1, c).Offset(0, 1).EntireColumn.Insert
Cells(1, c).Offset(0, 1).EntireColumn.Insert
End Sub

Originally Posted by sktneer

You may try this code also.....
The code will ask you to input the column of interest, so you need to input the column alphabet. Suppose you want to insert two column after the col. D, in that case you need to input either d or D in the input box.

Sub InsertColumn()
Dim c As String
'You need to input the column alphabet like d,e,f,g...etc
c = InputBox("After which column you want to insert new column?")
Cells(1, c).Offset(0, 1).EntireColumn.Insert
Cells(1, c).Offset(0, 1).EntireColumn.Insert
End Sub

Good idea but instead of giving the option to the user to choose the column,

(I repeat what I said above), if we give a name to the column from which the new columns will always be created before it will be easier. Can we do that in macro?

paint.jpg