I ran into this code when trying to copy some rows from one sheet to another in Excel through VBA. The widths would always display at the destination spreadsheet’s default column width. This code helps, all you need to pass it is the source and target ranges as parameters.
Private Sub copyColumnWidths(TargetRange As Range, SourceRange As Range) Dim c As Long With SourceRange For c = 1 To .Columns.Count TargetRange.Columns(c).ColumnWidth = .Columns(c).ColumnWidth Next c End With End Sub
copyColumnWidths Sheets("MySheet").Range("A1:AA500"), Sheets("YourSheet").Range("A1:AA500")