Same column widths applied to all sheets in a workbook

Milos

Board Regular
Joined
Aug 28, 2016
Messages
115
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):
ABCDE
2016.2017NewABC123YesFull
2016.2017OldABC123NoHalf
2016.2017NewDEF456NoHalf
2016.2017NewDEF456NoHalf
2017.2018OldGHI789NoHalf
2017.2018OldDEF456NoHalf

<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,
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Krayons

Board Regular
Joined
Sep 9, 2016
Messages
232
Try this:

Code:
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
 
Last edited:

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,078
If you select multiple sheets and resize a column on one of them, the other sheets will reflect that.
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,804
Here's another option:

Code:
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

Board Regular
Joined
Aug 28, 2016
Messages
115
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
 

Forum statistics

Threads
1,143,654
Messages
5,720,098
Members
422,266
Latest member
Mattyw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top