Auto Column width for new sheets in workbook?

  • Thread starter Thread starter J.W. Aldridge
  • Start date Start date
J

J.W. Aldridge

Is there any way I can preset the column width(s) for all new
sheets created in a workbook? Below are the measurements I need.

Columns("a:a").Select
Selection.ColumnWidth = 5.43
Columns("b:b").ColumnWidth = 57.14
Columns("b:b").EntireColumn.AutoFit
Columns("c:c").Select
Selection.ColumnWidth = 6.29
Columns("d:d").Select
Selection.ColumnWidth = 8.43
Columns("e:e").Select
Selection.ColumnWidth = 12.43
Columns("f:f").Select
Selection.ColumnWidth = 12.14
Columns("g:g").Select
Selection.ColumnWidth = 14.14
Columns("h:h").Select
Selection.ColumnWidth = 8
Columns("i:i").Select
Selection.ColumnWidth = 18.57
Columns("j:j").Select
Selection.ColumnWidth = 21.57
Columns("k:k").Select
Selection.ColumnWidth = 4.71
Columns("l:l").Select
Selection.ColumnWidth = 8.43




Thanx.,
 
Put this code in the ThisWorkbook module of the file. Note, however,
that your code sets column B to width 57.14, then you AutoFit column B
afterwards. You probably want to delete one of those statements.
Also, you don't need to select the columns before setting their widths.
In general, selecting things slows macros down.

Option Explicit

Private Sub Workbook_NewSheet(ByVal Sh As Object)
With Sh
.Columns("a:a").ColumnWidth = 5.43
.Columns("b:b").ColumnWidth = 57.14
.Columns("b:b").EntireColumn.AutoFit
.Columns("c:c").ColumnWidth = 6.29
.Columns("d:d").ColumnWidth = 8.43
.Columns("e:e").ColumnWidth = 12.43
.Columns("f:f").ColumnWidth = 12.14
.Columns("g:g").ColumnWidth = 14.14
.Columns("h:h").ColumnWidth = 8
.Columns("i:i").ColumnWidth = 18.57
.Columns("j:j").ColumnWidth = 21.57
.Columns("k:k").ColumnWidth = 4.71
Columns("l:l").ColumnWidth = 8.43
End With
End Sub


Mark
 
Back
Top