Inserting new column between the columns

[ram117]

Hi Everybody,

I wnat to insert a new column between the columns E&F i
multiple sheets (say sheet1 to sheet10) with a specified width, if an
body have the code Pl. Tell me.

Thanks and Regards

Raman

 

[Ken Johnson]

Ramana
Public Sub Insert_Col()
Dim Sht As Worksheet
For Each Sht In Worksheets
Sht.Range("F1").EntireColumn.Insert
Next
End Sub

Is this what you are after?
Ken Johnson

 

[Ken Johnson]

Hi Ramana,
Sorry, I left out the bit about column width!
How about:

Public Sub Insert_Col()
Dim Sht As Worksheet
For Each Sht In Worksheets
With Sht.Range("F1")
..EntireColumn.Insert
..Offset(0, -1).ColumnWidth = 12
End With
Next
End Sub

This makes the column width = 12, change according to your needs.
Ken Johnson

 

[Ken Johnson]

Hi yet again Ramana,

If you only want certain sheets to get the extra column between E&F
then this example adds the column to only the 2nd, 3rd, 4th and 5th
sheets, determined by the k values in the If statement:

Public Sub Insert_Col()
Dim Sht As Worksheet, k As Byte
For Each Sht In Worksheets
k = k + 1
If k >= 2 And k <= 5 Then
With Sht.Range("F1")
..EntireColumn.Insert
..Offset(0, -1).ColumnWidth = 12
End With
End If
Next
End Sub

 

[ram117]

Hi Ken,

thanks for the reply. While running the code it is giving me
runtime error at this line.

With sht.Range("f1").EntireColumn.Insert.Offset(0, -1).ColumnWidth =
12

It saying "object" required Pl. can you have a look into this.

Thanks and Regards

Ramana

 

[Ken Johnson]

Hi Ramana,
that is actually three separate lines of code:
With Sht.Range("F1")
..EntireColumn.Insert
..Offset(0,-1).ColumnWidth = 12
End With

Make sure you start a new line at .EntireColumn....... then another
new line at .Offset etc

I ran the code on my PC and it worked fine.

Let me know how you go.
Ken Johnson

 

[Guest]

Hi Ramana,

Select all the sheets you want to insert the new column in (you can select
multiple sheets by clicking on the sheet tabs by holding the CTRL button),
insert the new column and adjust the width. Anything you do on the top sheet
(data entry, formatting, ....... will reflect in ALL those selected sheets).
Now click on any one of those sheet tabs to get out of the multiple selection
mode.

Regards,
B. R. Ramachandran

 

[bobby]

Hi Ken,

Thanks for looking into my problem now it is working fine.

Thanks and Regards

Ramana

 

[Ken Johnson]

Hi Ramana,
Glad to be of assistance.
Ken Johnson