Dynamic column insert (macro)

C

Chris

Hi

I have a table and I wish to create a macro to add a column to my "table".
However if i use the insert function it isn't dynamic and would always
insert in the same column. I have set out below a rough idea of what I would
like.

ie.

Column A B C D

Macro (Run once)

Column A B C D E

Macro (Run Twice)

Column A B C D E F

Right now if I run my macro twice i get the follwing.

Column A B C D F E

I think the key is for a macro to recognise a cell and then being able to
select the whole column to which that cell belongs.



Thanks
 
D

davesexcel

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Cancel = True 'Eliminate Edit status due to doubleclick
Target.Offset(1).EntireColumn.Insert
Target.EntireColumn.Copy Target.Offset(1).EntireColumn
On Error Resume Next
Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
On Error GoTo 0
End Sub

Here's a Worksheet macro, you will need to place it in a worksheet
module, of course it doesn't have to be a double click event, you can
move it to a button. You can also modify it to find the last column as
well
 
Top