macro help

[g3jimha]

hi there, im currently trying to make a macro to start automatically when the a file is opened, its just really to change the default column width in excel. but when i try and run it i just get errors all the time. I'm using excel 2007. My code is


[VBA]Sub Auto_Open()
'
' Auto_Open Macro
'
Columns("A:Z").select
Range("Z1").Activate
selection.coulmnwidth=20
with selection
.horizontalAlignment=x1Left
.VerticalAlignment= x1Bottom
.Orientation=0
.WrapText=False
.IndentLevel=0
.ShrinkToFit=False
.ReadingOrder=xlContext
.MergeCells=False
end with
activeWindow.LargeScroll ToRight:=-1
Range("K1").select
activeWindow.smallScroll Down:=-15
End Sub[/VBA]


And the Error is "Method 'Columns' of object '_Global' failed"

if anyone could help thatd be great thanks , Jim

[Rob342]

g3

This should work

Put the routine in the Workbook open event
[VBA]
Option Explicit
Private Sub Workbook_Open()
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
With ws
.Range("A1").ColumnWidth = 20
End With
End Sub
[/VBA]

Rob

[Bob Phillips]

This should work

[vba]

Sub Auto_Open()
With Worksheets(1)

.Activate

With .Columns("A:Z")

.ColumnWidth = 20
.HorizontalAlignment = x1Left
.VerticalAlignment = x1Bottom
.Orientation = 0
.WrapText = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

.Range("K1").Select
End With
End SUb[/vba]

[g3jimha]

thanks very much lads for the speedy replys, they both worked, i dont trust that macro record thing now

[Aussiebear]

Its not a matter of not trusting it, but more about when to adjust what its tellling you