Set column widths to match specified sheet

[Sean Bartleet]

Hi,

I am would like to write some code to set the column width of all selected
worksheets to match the column widths in a specified sheet. Does anyone know
of any existing code or an add-in (free) that does this?

I often use multipage workbooks and can easily set the columns on all sheets
to be the same on all sheets by selecting all sheets and changing the column
width. Problems arise when the columns are later changed and I find myself
forever resetting the column widths.

Any assistance would be appreciated.

Regards.

Sean

 

[dominicb]

Good morning Sean Bartleet

I have a free add-in available to anyone requesting it which wil
(amongst other things) copy and paste cell widths across files. If yo
would like this please send me an e-mail.

HTH

DominicB
(e-mail address removed)

 

[Leith Ross]

Hello Sean,

Dominic kindly offered you his Add-in in reponse to one part of your
request. I am offering the you the code portion in response to the
second half of your request.

This macro will ask you if you want to apply the active sheet's column
widths to the other sheets in the workbook. If the answer is No, it
does nothing. Insert a VBA module into your project and copy this code
into. You can run the macro from the Macro List (use ALT + F8 to bring
up the list).


Code:
--------------------
Public Sub SetColumnWidths()

Dim Answer, Col, Sht
Dim MasterRng As Range
Dim MasterSht As String
Dim Msg As String

Msg = "Do you want to apply this Worksheet's" & vbCrLf _
& "Column Widths to all the Worksheets?"
Answer = MsgBox(Msg, vbInformation + vbYesNo)
If Answer = vbNo Then Exit Sub

MasterSht = ActiveSheet.Name
Set MasterRng = ActiveSheet.Range("A1", "IV1")

For Each Sht In Worksheets
If Sht.Name <> MasterSht Then
For Each Col In MasterRng
Sht.Range(Col.Address).ColumnWidth = Col.ColumnWidth
Next Col
End If
Next Sht

End Sub

 

[Sean]

Leith, Thanks for the code.
Dominic, thanks for the add in, quite excellent.

I really appreciate both.

Sean