PDA

View Full Version : AutoFit Columns



sooty8
09-23-2011, 04:27 AM
Hi

I have used the macro recorder code below



Sub AutoFitSheets()
'
' AutoFitSheets Macro
'


'
Sheets("Sheet2").Select

Columns("A:K").Select

Selection.Columns.AutoFit

Sheets("Sheet3").Select

Columns("A:K").Select

Selection.Columns.AutoFit

Sheets("Sheet4").Select

Columns("A:K").Select

Selection.Columns.AutoFit

Sheets("Sheet5").Select

Columns("A:K").Select

Selection.Columns.AutoFit

Sheets("Sheet6").Select

Columns("A:K").Select
Selection.Columns.AutoFit
End
Sub


my question is that I really don't know how many sheets will be in the workbook it could be 2 Sheets to a maximum of 25 Sheets is it possible to just have Autofit run on all sheets within the workbook excluding Sheet1 which is the master sheet for the workbook.

Regards

Sooty 8

Kenneth Hobs
09-23-2011, 05:27 AM
Sub AutoFitAllColsAtoK()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Range("A1:K1").Columns.AutoFit
Next ws
End Sub

sooty8
09-23-2011, 06:08 AM
Hi Kenneth

Thanks for the reply, I really need to exclude sheet 1 from also running the AutoFit because of the format of that sheet the column width's are set to accomodate more data - is this possible.

Regards

Sooty8

Kenneth Hobs
09-23-2011, 06:21 AM
Sub AutoFitAllColsAtoK()
Dim ws As Worksheet
For Each ws In Worksheets
If Not ws.Name = "Sheet1" Then ws.Range("A1:K1").Columns.AutoFit
Next ws
End Sub

sooty8
09-23-2011, 07:57 AM
Hi Kenneth

Many thanks works spot on.

Regards

Sooty8