Formatting multiple sheets at once

[zookeepertx]

I'm trying to format 2 worksheets at the same time. Part of the formatting is working on both sheets, but the column widths are not. I've tried every syntax change I can think of but obviously I'm missing something!
The current code is below; the 2 sheets are called "Combined" and "Comb-Ocean". The text color, cell colors, the borders are all formatting like you'd expect; it's ONLY the column widths that are the problem. The formatting is working correctly on "Combined" but not on "Comb-Ocean". This is all part of a larger macro, but I've isolated this part, since it's the only part that's being a pain.

Sub Macro15()
' Macro15 Macro
    Sheets(Array("Combined", "Comb-Ocean")).Select
    Cells.Select
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With Selection.Borders
        .LineStyle = xlNone
    End With
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

Selection.RowHeight = 12
With Selection
    Range("A:B").EntireColumn.AutoFit
    Columns("C:D").ColumnWidth = 3.29
    Columns("E:E").ColumnWidth = 4.17
    Columns("F:F").ColumnWidth = 9
    Columns("G:G").ColumnWidth = 22.14
    Columns("H:H").ColumnWidth = 19.86
    Columns("I:W").ColumnWidth = 6.29
End With
End Sub

There are numerous other worksheets in the workbook but I only need to format these 2.

Can someone tell me what's wrong with this?

Thank you.

Jenny

 

[Fluff]

You will need to do the columns for each sheet individually.

 

[zookeepertx]

You will need to do the columns for each sheet individually.

I was afraid of that, but am at a loss as to why the rest of the formatting - text color, etc - works perfectly on both sheets at once but the column width doesn't. Row height even works! That's just weird, LOL! (Of course, when I stop running across things in Excel that are weird, the shock may kill me. )

 

[Fluff]

I've no idea why it works that way, but some types of formatting cannot be done on multiple sheets at once.

 

[EXCEL MAX]

Selection can works only on one sheet.

 

[Fluff]

As the OP has already said all the code works except the column widths, your comment is obviously inaccurate.

 

[zookeepertx]

I've no idea why it works that way, but some types of formatting cannot be done on multiple sheets at once.

Okay, so I changed it to this and it works correctly, even when inserted into the large, entire macro. Guess it's one of those things I'll just have to accept that its odd and have it coded this way, since "the Excel creators" don't listen to me, .

Sub Macro15()
' Macro15 Macro
Dim ws As Worksheet
    For Each ws In Sheets(Array("Combined", "Comb-Ocean"))
    ws.Activate
 
    Cells.Select
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With Selection.Borders
        .LineStyle = xlNone
    End With
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

    Selection.RowHeight = 12
    With Selection
        Range("A:B").EntireColumn.AutoFit
        Columns("C:D").ColumnWidth = 3.29
        Columns("E:E").ColumnWidth = 4.17
        Columns("F:F").ColumnWidth = 9
        Columns("G:G").ColumnWidth = 22.14
        Columns("H:H").ColumnWidth = 19.86
        Columns("I:W").ColumnWidth = 6.29
    End With
Next ws
End Sub

Thank you for your most excellent help!

Jenny

 

[Fluff]

Glad to help & thanks for the feedback.