Easier way to insert columns
 

Hi,

I need to insert 40-50 columns and label the headers, at the moment I am
using the following code in a macro:

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "Status"
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C1").Select
ActiveCell.FormulaR1C1 = "Details"

etc

Is there an easier way to write this?

Thanks!

Excel 2002

Easier way to insert columns
 

You can get rid of the selects...

Columns("A:A").Insert Shift:=xlToRight
Range("A1").Formula = "Status"
Columns("C:C").Insert Shift:=xlToRight
Range("C1").Formula = "Details"

--
HTH...

Jim Thomlinson


"sanj" wrote:

Hi,

I need to insert 40-50 columns and label the headers, at the moment I am
using the following code in a macro:

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "Status"
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C1").Select
ActiveCell.FormulaR1C1 = "Details"

etc

Is there an easier way to write this?

Thanks!

Excel 2002

Easier way to insert columns
 

Thanks Jim!


"Jim Thomlinson" wrote in message
...
You can get rid of the selects...

Columns("A:A").Insert Shift:=xlToRight
Range("A1").Formula = "Status"
Columns("C:C").Insert Shift:=xlToRight
Range("C1").Formula = "Details"

--
HTH...

Jim Thomlinson


"sanj" wrote:

Hi,

I need to insert 40-50 columns and label the headers, at the moment I am
using the following code in a macro:

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "Status"
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C1").Select
ActiveCell.FormulaR1C1 = "Details"

etc

Is there an easier way to write this?

Thanks!

Excel 2002

Easier way to insert columns
 

Maybe overkill but this might help if you need to run it again on other
worksheets/books:

Best regards

John

Sub Check()

Dim sLabel As String

Columns("A:AN").Select '40 columns wide
Selection.Insert Shift:=xlToRight
Range("A1").Select

For x = 1 To 40
Select Case x
Case 1
sLabel = "Status"
Case 2
sLabel = "Details"
Case 3
sLabel = "Something Else...."
'Keep adding your labels here 1 to 40
End Select

Application.ActiveWorkbook.ActiveSheet.Cells(1, x).FormulaR1C1 =
sLabel
sLabel = ""
Next x
End Sub


"sanj" wrote in message
...
Hi,

I need to insert 40-50 columns and label the headers, at the moment I am
using the following code in a macro:

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "Status"
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C1").Select
ActiveCell.FormulaR1C1 = "Details"

etc

Is there an easier way to write this?

Thanks!

Excel 2002

Easier way to insert columns
 

Hi Sanj.

As another suggestion:

Sub TestIt2()
Dim i As Long, j As Long
Dim arr As Variant

arr = Array("Status", "Details", "Header3", "Header4", "Header5", _
"Header6", "Header7", "Header8", "Header9", "Header10", _
"Header11", "Header12", "Header13", "Header14", "Header15", _
"Header16", "Header17", "Header18", "Header19", "Header20") '... etc

For i = 1 To (UBound(arr) - LBound(arr) + 1) * 2 Step 2
j = j + 1
Columns(i).Insert
Columns(i).Cells(1).Value = arr(j - 1)
Next

End Sub

---
Regards,
Norman



"sanj" wrote in message
...
Hi,

I need to insert 40-50 columns and label the headers, at the moment I am
using the following code in a macro:

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "Status"
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C1").Select
ActiveCell.FormulaR1C1 = "Details"

etc

Is there an easier way to write this?

Thanks!

Excel 2002

Easier way to insert columns
 

Thanks Norman,

where do I define where I need to the columns to be inserted i.e. Status
needs to be inserted in Column A, the Details need to be the header for a
new column inserted into column C etc

Regards,

Sanj



"Norman Jones" wrote in message
...
Hi Sanj.

As another suggestion:

Sub TestIt2()
Dim i As Long, j As Long
Dim arr As Variant

arr = Array("Status", "Details", "Header3", "Header4", "Header5", _
"Header6", "Header7", "Header8", "Header9", "Header10", _
"Header11", "Header12", "Header13", "Header14", "Header15", _
"Header16", "Header17", "Header18", "Header19", "Header20") '...
etc

For i = 1 To (UBound(arr) - LBound(arr) + 1) * 2 Step 2
j = j + 1
Columns(i).Insert
Columns(i).Cells(1).Value = arr(j - 1)
Next

End Sub

---
Regards,
Norman



"sanj" wrote in message
...
Hi,

I need to insert 40-50 columns and label the headers, at the moment I am
using the following code in a macro:

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "Status"
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C1").Select
ActiveCell.FormulaR1C1 = "Details"

etc

Is there an easier way to write this?

Thanks!

Excel 2002

Easier way to insert columns
 

Hi Sanji,

where do I define where I need to the columns to be inserted i.e. Status
needs to be inserted in Column A, the Details need to be the header for a
new column inserted into column C etc


That is all done in my code.

Try the code on a copy of your workbook to see and test.

All you need to do, is to replace the Header3, Header4...Header20... values
with your intended header values.

a column will be inserted for each header value that you supply.


---
Regards,
Norman



"sanj" wrote in message
...
Thanks Norman,

where do I define where I need to the columns to be inserted i.e. Status
needs to be inserted in Column A, the Details need to be the header for a
new column inserted into column C etc

Regards,

Sanj



"Norman Jones" wrote in message
...
Hi Sanj.

As another suggestion:

Sub TestIt2()
Dim i As Long, j As Long
Dim arr As Variant

arr = Array("Status", "Details", "Header3", "Header4", "Header5", _
"Header6", "Header7", "Header8", "Header9", "Header10", _
"Header11", "Header12", "Header13", "Header14", "Header15", _
"Header16", "Header17", "Header18", "Header19", "Header20") '...
etc

For i = 1 To (UBound(arr) - LBound(arr) + 1) * 2 Step 2
j = j + 1
Columns(i).Insert
Columns(i).Cells(1).Value = arr(j - 1)
Next

End Sub

---
Regards,
Norman



"sanj" wrote in message
...
Hi,

I need to insert 40-50 columns and label the headers, at the moment I
am
using the following code in a macro:

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "Status"
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C1").Select
ActiveCell.FormulaR1C1 = "Details"

etc

Is there an easier way to write this?

Thanks!

Excel 2002

Easier way to insert columns
 

Thanks Norman,

I can see how this works, the column is inserted after every other columns,
however, I need the columns to be inserted at particular points such as
A, C, H, I, J AA, AF etc is it possible to add another array for where the
columns need to be inserted or a multidimensional array

arr = Array([A, "Status"], [C, "Details"], [H, "Header3"], [I,
"Header4"}

Regards,

Sanj



"Norman Jones" wrote in message
...
Hi Sanji,

where do I define where I need to the columns to be inserted i.e. Status
needs to be inserted in Column A, the Details need to be the header for
a
new column inserted into column C etc


That is all done in my code.

Try the code on a copy of your workbook to see and test.

All you need to do, is to replace the Header3, Header4...Header20...
values
with your intended header values.

a column will be inserted for each header value that you supply.


---
Regards,
Norman



"sanj" wrote in message
...
Thanks Norman,

where do I define where I need to the columns to be inserted i.e. Status
needs to be inserted in Column A, the Details need to be the header for
a
new column inserted into column C etc

Regards,

Sanj



"Norman Jones" wrote in message
...
Hi Sanj.

As another suggestion:

Sub TestIt2()
Dim i As Long, j As Long
Dim arr As Variant

arr = Array("Status", "Details", "Header3", "Header4", "Header5", _
"Header6", "Header7", "Header8", "Header9", "Header10", _
"Header11", "Header12", "Header13", "Header14", "Header15", _
"Header16", "Header17", "Header18", "Header19", "Header20")
'...
etc

For i = 1 To (UBound(arr) - LBound(arr) + 1) * 2 Step 2
j = j + 1
Columns(i).Insert
Columns(i).Cells(1).Value = arr(j - 1)
Next

End Sub

---
Regards,
Norman



"sanj" wrote in message
...
Hi,

I need to insert 40-50 columns and label the headers, at the moment I
am
using the following code in a macro:

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "Status"
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C1").Select
ActiveCell.FormulaR1C1 = "Details"

etc

Is there an easier way to write this?

Thanks!

Excel 2002

Easier way to insert columns
 

Hi Sanj,

I need the columns to be inserted at particular points such as
A, C, H, I, J AA, AF etc

Try:

Sub TestIt2A()
Dim i As Long, j As Long
Dim arr As Variant
Dim arr2 As Variant

arr = Array("Status", "Details", "Header3", "Header4", "Header5", _
"Header6", "Header7", "Header8", "Header9", "Header10", _
"Header11", "Header12", "Header13", "Header14", "Header15", _
"Header16", "Header17", "Header18", "Header19", "Header20") '... etc

arr2 = Array("A", "C", "H", "I", "J", "AA", "AF") '... etc

For i = 1 To (UBound(arr2) - LBound(arr2) + 1) Step 1
Columns(arr2(i - 1)).Insert
Columns(arr2(i - 1)).Cells(1).Value = arr(i - 1)
Next

End Sub


---
Regards,
Norman



"sanj" wrote in message
...
Thanks Norman,

I can see how this works, the column is inserted after every other
columns,
however, I need the columns to be inserted at particular points such as
A, C, H, I, J AA, AF etc is it possible to add another array for where the
columns need to be inserted or a multidimensional array

arr = Array([A, "Status"], [C, "Details"], [H, "Header3"], [I,
"Header4"}

Regards,

Sanj



"Norman Jones" wrote in message
...
Hi Sanji,

where do I define where I need to the columns to be inserted i.e.
Status
needs to be inserted in Column A, the Details need to be the header for
a
new column inserted into column C etc


That is all done in my code.

Try the code on a copy of your workbook to see and test.

All you need to do, is to replace the Header3, Header4...Header20...
values
with your intended header values.

a column will be inserted for each header value that you supply.


---
Regards,
Norman



"sanj" wrote in message
...
Thanks Norman,

where do I define where I need to the columns to be inserted i.e.
Status
needs to be inserted in Column A, the Details need to be the header for
a
new column inserted into column C etc

Regards,

Sanj



"Norman Jones" wrote in message
...
Hi Sanj.

As another suggestion:

Sub TestIt2()
Dim i As Long, j As Long
Dim arr As Variant

arr = Array("Status", "Details", "Header3", "Header4", "Header5",
_
"Header6", "Header7", "Header8", "Header9", "Header10", _
"Header11", "Header12", "Header13", "Header14", "Header15", _
"Header16", "Header17", "Header18", "Header19", "Header20")
'...
etc

For i = 1 To (UBound(arr) - LBound(arr) + 1) * 2 Step 2
j = j + 1
Columns(i).Insert
Columns(i).Cells(1).Value = arr(j - 1)
Next

End Sub

---
Regards,
Norman



"sanj" wrote in message
...
Hi,

I need to insert 40-50 columns and label the headers, at the moment
I
am
using the following code in a macro:

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "Status"
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C1").Select
ActiveCell.FormulaR1C1 = "Details"

etc

Is there an easier way to write this?

Thanks!

Excel 2002

Easier way to insert columns
 

Norman,

Thats perfect and will save me alot of time (as well as being educated!)

Thanks!

Sanjay


"Norman Jones" wrote in message
...
Hi Sanj,

I need the columns to be inserted at particular points such as
A, C, H, I, J AA, AF etc

Try:

Sub TestIt2A()
Dim i As Long, j As Long
Dim arr As Variant
Dim arr2 As Variant

arr = Array("Status", "Details", "Header3", "Header4", "Header5", _
"Header6", "Header7", "Header8", "Header9", "Header10", _
"Header11", "Header12", "Header13", "Header14", "Header15", _
"Header16", "Header17", "Header18", "Header19", "Header20") '...
etc

arr2 = Array("A", "C", "H", "I", "J", "AA", "AF") '... etc

For i = 1 To (UBound(arr2) - LBound(arr2) + 1) Step 1
Columns(arr2(i - 1)).Insert
Columns(arr2(i - 1)).Cells(1).Value = arr(i - 1)
Next

End Sub


---
Regards,
Norman



"sanj" wrote in message
...
Thanks Norman,

I can see how this works, the column is inserted after every other
columns,
however, I need the columns to be inserted at particular points such as
A, C, H, I, J AA, AF etc is it possible to add another array for where
the
columns need to be inserted or a multidimensional array

arr = Array([A, "Status"], [C, "Details"], [H, "Header3"], [I,
"Header4"}

Regards,

Sanj



"Norman Jones" wrote in message
...
Hi Sanji,

where do I define where I need to the columns to be inserted i.e.
Status
needs to be inserted in Column A, the Details need to be the header
for
a
new column inserted into column C etc


That is all done in my code.

Try the code on a copy of your workbook to see and test.

All you need to do, is to replace the Header3, Header4...Header20...
values
with your intended header values.

a column will be inserted for each header value that you supply.


---
Regards,
Norman



"sanj" wrote in message
...
Thanks Norman,

where do I define where I need to the columns to be inserted i.e.
Status
needs to be inserted in Column A, the Details need to be the header
for
a
new column inserted into column C etc

Regards,

Sanj



"Norman Jones" wrote in message
...
Hi Sanj.

As another suggestion:

Sub TestIt2()
Dim i As Long, j As Long
Dim arr As Variant

arr = Array("Status", "Details", "Header3", "Header4",
"Header5",
_
"Header6", "Header7", "Header8", "Header9", "Header10", _
"Header11", "Header12", "Header13", "Header14", "Header15",
_
"Header16", "Header17", "Header18", "Header19", "Header20")
'...
etc

For i = 1 To (UBound(arr) - LBound(arr) + 1) * 2 Step 2
j = j + 1
Columns(i).Insert
Columns(i).Cells(1).Value = arr(j - 1)
Next

End Sub

---
Regards,
Norman



"sanj" wrote in message
...
Hi,

I need to insert 40-50 columns and label the headers, at the
moment
I
am
using the following code in a macro:

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "Status"
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C1").Select
ActiveCell.FormulaR1C1 = "Details"

etc

Is there an easier way to write this?

Thanks!

Excel 2002