Excel: code to insert column not working

[Juddy58]

Hello, i have a workbook with around 50 sheets. Im trying to create a macro that runs through the work book and adds columns and rows in certain areas on certain sheets.
In one of the sheets "bud check" a new column is inserted in column "G" and values are set using the following.

With Sheets("bud check")
.Select
.Range("D2").Select
strColumnLetter = Selection

.Columns("G:G").Select
Selection.Insert Shift:=xlToRight

.Range("G4").Select
Selection.FormulaR1C1 = strWardName

.Range("G11") = "=SUM(G5:G10)"

.Range("G14") = "=SUM(G12:G13)"
end with

Because the code is run from a different sheet i use a 'with' statement to select the appropriate sheet.
This works fine, a new column is created with no problems. Where i do have a problem is that im trying to do a similar thing in another sheet "allied health report". In this sheet i try to insert a new column in "E" using:
With Sheets("allied health report")
.Select

.Columns("E:E").Select
Selection.Insert Shift:=xlToRight

.Range("E4").Select
Selection.FormulaR1C1 = strWardName

.Range("E11") = "=SUM(E5:E10)"

.Range("E14") = "=SUM(E12:E13)"
end with

When this code is run it inserts 15 blank columns between columns A and O, where i only want one new column to be added in "E"

I have also tried recording a macro and using its code:
Sub Macro4()
' Macro4 Macro
' Macro recorded 25/05/2004
'
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("allied health report").Select
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
End Sub
When i run this i end up with an error message:
Run-time error'1004':
Select method of range class failed
this occurs on line - Columns("E:E").select

I have no idea why the first example of code im using doesn't work correctly. it works perfectly in my "bud check" sheet, could there be a setting in the "allied health report" sheet that is causing the issue, i have done a search and come up with nothing so far.
Thanks
Justin

 

[BrainB]

Perhaps it is time to start getting away from using Select with something like, which does not require the sheet to be active :-

Worksheets("allied health report").Columns _
("E").EntireColumn.Insert shift:=xlToRight

Regards
BrianB
Use CupOfCoffee to speed up all windows applications
================================

 

[Juddy58]

Thanks for the reply brainb, i just figured it out before reading your post.
The reason why it wouldn't work is that one of my subheadings in the sheet was set to center across columns, so simple and obvious yet it took me so long to solve.
Thanks
Justin