Same column widths applied to all sheets in a workbook

[Milos]

Hi all,

I have got a printing project that I am trying to save days worth of time in.

Currently I have a list of contents in 'Sheet1' (example below):

A	B	C	D	E
2016.2017	New	ABC123	Yes	Full
2016.2017	Old	ABC123	No	Half
2016.2017	New	DEF456	No	Half
2016.2017	New	DEF456	No	Half
2017.2018	Old	GHI789	No	Half
2017.2018	Old	DEF456	No	Half

<tbody>
</tbody>

The column widths (in excel) for A, B, C and E is currently 10 and for column D it is 20.

I have some code that will allow me to put this list of contents into multiple new sheets based on the name in column C. So 'Sheet1' automatically generates three more sheets beside it named: 'ABC123', 'DEF456' and 'GHI789'.

What I now need is some way to make all the new sheets ('ABC123', 'DEF456', 'GHI789') to have the same column width as 'Sheet1'. Does anybody know how to do this? As the examples I have seen online always refer to specific sheet names but my sheet names will change repeatedly.

Thus I need a code that will format all of the sheets in the entire workbook ('Sheet1', 'ABC123', 'DEF456' and 'GHI789') to have column widths of 10 (for columns A, B, C and E) and 20 (for column D).

Any help is greatly appreciated!

Thanks,

 

[Krayons]

Try this:

Sub setColWidths()

Dim ws As Worksheet


For Each ws In ThisWorkbook.Worksheets


    Sheets(ws).Columns("A").ColumnWidth = 10
    Sheets(ws).Columns("B").ColumnWidth = 10
    Sheets(ws).Columns("C").ColumnWidth = 10
    Sheets(ws).Columns("D").ColumnWidth = 20
    Sheets(ws).Columns("E").ColumnWidth = 10


Next

End Sub

 

[mikerickson]

If you select multiple sheets and resize a column on one of them, the other sheets will reflect that.

 

[Trebor76]

Here's another option:

Option Explicit
Sub Macro1()

    Dim wsMySheet As Worksheet
    
    Application.ScreenUpdating = False
    
    For Each wsMySheet In ThisWorkbook.Sheets
        With wsMySheet
            .Range("A:C,E:E").ColumnWidth = 10
            .Range("D:D").ColumnWidth = 20
        End With
    Next wsMySheet
    
    Application.ScreenUpdating = True
    
End Sub

Robert

 

[Milos]

Thanks guys,

Trebor76 your code worked perfectly for me!

Here is my code so far:

Sub SetWorkbookAttributes()
For Each xWorksheet In ActiveWorkbook.Worksheets
xWorksheet.PageSetup.Orientation = _
ThisWorkbook.Worksheets("Bulk printing macro").PageSetup.Orientation
Next xWorksheet

Dim wsMySheet As Worksheet

Application.ScreenUpdating = False

For Each wsMySheet In ThisWorkbook.Sheets
With wsMySheet
.Range("A:A").ColumnWidth = 15
.Range("B:B").ColumnWidth = 11
.Range("C:C,E:E").ColumnWidth = 7
.Range("F:H").ColumnWidth = 18
.Range("J:J").ColumnWidth = 10
.Range("A:J").WrapText = True
End With
Next wsMySheet

Application.ScreenUpdating = True
End Sub