Auto Column width for new sheets in workbook?

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.,
 
M

Mark Driscol

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top