Dynamically add column to right of last column

[Andy2011]

Hi

I have used the following code to dynamically add a column to the right of the last column at the time of running the code. It seems to work, however if I then delete one or more of the columns the macro will not work again unless I save the spreadsheet first.


Sub InsertColumn()

Dim lastColumn As Long
lastColumn = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Column
Columns(lastColumn).Copy Destination:=Columns(lastColumn + 1)

End Sub



Anyone got any ideas why?

[mancubus]

repeat detecting the last column.

[Andy2011]

Sorry can you please explain as I don't understand. Apologies for being thick.

[JKwan]

run this line again to get the last column again


lastColumn = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Column

[snb]

with cells(1,columns.count).end(xltoleft).entirerow
.offset(,1).value=.value
end with

[chlin]

Hi snb,

It should be



With Cells(1, Columns.Count).End(xlToLeft).EntireColumn
.Offset(, 1).Value = .Value
End With

[snb]

@chlin

You are right !