VBA to select all columns in a worksheet and auto adjust all columns width in Excel 2010
Try this...
Sheets(1).UsedRange.Columns.AutoFit
You can use
Cells.Columns.Autofit
or, if you're not on the ActiveSheet then
Sheets(name_or_number).Columns.Autofit