Automating Excel Formatting
How to Automate Formatting Using VBA in Excel
Rather than manually setting column widths, Zoom percentage or currency formats on an Excel sheet, a macro can save time and effort.
Automating Column Width in Excel
Columns in an Excel spreadsheet can be automated in code to set width, format, font, etc. "on the fly" for your automated reports.
Column Width:
This code in a VBA Macro will set the width of each of the columns.
Public Sub ColumnSettings()
'set widths
Columns("A:A").Select
Selection.ColumnWidth = 13
Columns("B:B").Select
Selection.ColumnWidth = 27
Columns("C:C").Select
Selection.ColumnWidth = 29
Columns("D:D").Select
Selection.ColumnWidth = 35
Columns("E:E").Select
Selection.ColumnWidth = 8.5
Columns("F:F").Select
Selection.ColumnWidth = 20
Columns("G:G").Select
Selection.ColumnWidth = 13
Columns("H:H").Select
Selection.ColumnWidth = 22
End Sub
Automating Excel Zoom Property Setting
This code will automatically set the zoom setting for the worksheet(s).
Public Sub ZoomSetting()
'select sheet and set zoom
Sheets("Data").Select
ActiveWindow.Zoom = 80
End Sub
Automate Excel Freeze Top Row
Freeze the top row so that the data can be scrolled through.
Public Sub TopRow()
'freeze the top row
Rows("1:2").Select
Range("A2").Activate
ActiveWindow.FreezePanes = True
End Sub
Setting Excel Currency Format Setting in Code
To set the format for the Sales YTD column.
Public Sub ColCurrFormat()
Columns("G:G").Select
Selection.NumberFormat = "$#,##0.00"
End Sub
Put the various procedures into the ThisWorkbook code:
Public Sub GenRept()
delSheets
PopWksht
DynamicHeader
ColumnSettings
ZoomSetting
TopRow
ColCurrFormat
End Sub
And when you run the macro, the perfectly formatted data will automatically be set.
Automating the formatting makes report creation much faster and easier.