Macro to insert column
 

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

Macro to insert column
 

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

"Colin Foster" wrote in message
...
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

Macro to insert column
 

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


"Colin Foster" wrote in message
...
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

Macro to insert column
 

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



"Tomek" wrote in message
...
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

Macro to insert column
 

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
"Colin Foster" wrote in message
...
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