PDA

View Full Version : macro help



g3jimha
08-10-2011, 05:31 AM
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


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


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

if anyone could help thatd be great thanks :), Jim

Rob342
08-10-2011, 05:46 AM
g3

This should work

Put the routine in the Workbook open event

Option Explicit
Private Sub Workbook_Open()
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
With ws
.Range("A1").ColumnWidth = 20
End With
End Sub


Rob

Bob Phillips
08-10-2011, 05:47 AM
This should work



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

g3jimha
08-10-2011, 06:55 AM
thanks very much lads for the speedy replys, they both worked, i dont trust that macro record thing now :)

Aussiebear
08-12-2011, 03:45 AM
Its not a matter of not trusting it, but more about when to adjust what its tellling you