Macro to insert column

[Colin Foster]

Hello,
I have a worksheet which records special prices for certain customers, all
other customers using a fixed price on the main worksheet within the
workbook.

If a customer now needs to be included in those with the special price, then
their details have to be included in the Special Prices sheet.

The way that I do this at present is to inseert a column, add in some
references to ensure that they are included in the various lookup tables and
then input their specific information.

I now want someone else to be able to do this for me, so need to automate
the insertion of the columns and setting up of the references for the
lookups. I have been able to do this via a macro, however, there is one part
that I am having a problem with and that is the insertion of the column. If
I record the macro, then it lets me insert in (say) column "P", however,
next time I will want this to be column "Q". Is there a way of getting Excel
to either prompt for a column reference (in a similar way to a parameter
query in Access), or is there an easy way to find the last used column and
then move back one before inserting?

Regards
Colin Foster

 

[Neil]

Colin,

This will give you the last used column in Row 1

Dim lastColumn As Long
lastColumn = Cells(1, 100).End(xlToLeft).Column
MsgBox lastColumn

Neil

 

[Tomek]

Hi,
here it goes:

Dim i As Integer
Dim t As Range

Set t = Application.InputBox("Click the last column", "Some Title Here",
Type:=8) ' 1)
i = t.Column
Columns(i).EntireColumn.Insert

i = Application.InputBox("Column number?", "Some Title Here", Type:=1)
' 2)
Columns(i).EntireColumn.Insert

i = Range("IV1").End(xlToLeft).Column
' 3) finds the last column without asking the user
Columns(i).EntireColumn.Insert

i = Cells(1, 256).End(xlToLeft).Column
' 4) same here
Columns(i).EntireColumn.Insert

regards
Tomek

 

[Tomek]

some autoformatting spoiled the layout.... it should look like this:

Dim i As Integer
Dim t As Range

' 1)
Set t = Application.InputBox("Click the last column", "Some Title Here", _
Type:=8)
i = t.Column
Columns(i).EntireColumn.Insert

' 2)
i = Application.InputBox("Column number?", "Some Title Here", Type:=1)
Columns(i).EntireColumn.Insert

' 3) finds the last column without asking the user
i = Range("IV1").End(xlToLeft).Column
Columns(i).EntireColumn.Insert

' 4) same here
i = Cells(1, 256).End(xlToLeft).Column
Columns(i).EntireColumn.Insert

 

[Colin Foster]

Thanks to both Neil & Tomek...not had chance to ry out either of your
suggestions, yet, but will do soon. Thanks for your input
Regards
Colin