Excel VBA For Each Worksheet Loop

each, excel, for-loop, vba, worksheet

Solution

Try to slightly modify your code:

Sub forEachWs()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        Call resizingColumns(ws)
    Next
End Sub

Sub resizingColumns(ws As Worksheet)
    With ws
        .Range("A:A").ColumnWidth = 20.14
        .Range("B:B").ColumnWidth = 9.71
        .Range("C:C").ColumnWidth = 35.86
        .Range("D:D").ColumnWidth = 30.57
        .Range("E:E").ColumnWidth = 23.57
        .Range("F:F").ColumnWidth = 21.43
        .Range("G:G").ColumnWidth = 18.43
        .Range("H:H").ColumnWidth = 23.86
        .Range("i:I").ColumnWidth = 27.43
        .Range("J:J").ColumnWidth = 36.71
        .Range("K:K").ColumnWidth = 30.29
        .Range("L:L").ColumnWidth = 31.14
        .Range("M:M").ColumnWidth = 31
        .Range("N:N").ColumnWidth = 41.14
        .Range("O:O").ColumnWidth = 33.86
    End With
End Sub

Note, `resizingColumns` routine takes parametr - worksheet to which Ranges belongs.

Basically, when you're using `Range("O:O")` - code operats with range from ActiveSheet, that's why you should use `With ws` statement and then `.Range("O:O")`.

And there is no need to use global variables (unless you are using them somewhere else)

Problem

I am working on code to basically go through each sheet in my Workbook, and then update column widths. Below is the code I wrote; I don't receive any errors, but it also doesn't actually do anything. Any help is greatly appreciated! ``` Option Explicit Dim ws As Worksheet, a As Range Sub forEachWs() For Each ws In ActiveWorkbook.Worksheets Call resizingColumns Next End Sub Sub resizingColumns() Range("A:A").ColumnWidth = 20.14 Range("B:B").ColumnWidth = 9.71 Range("C:C").ColumnWidth = 35.86 Range("D:D").ColumnWidth = 30.57 Range("E:E").ColumnWidth = 23.57 Range("F:F").ColumnWidth = 21.43 Range("G:G").ColumnWidth = 18.43 Range("H:H").ColumnWidth = 23.86 Range("i:I").ColumnWidth = 27.43 Range("J:J").ColumnWidth = 36.71 Range("K:K").ColumnWidth = 30.29 Range("L:L").ColumnWidth = 31.14 Range("M:M").ColumnWidth = 31 Range("N:N").ColumnWidth = 41.14 Range("O:O").ColumnWidth = 33.86 End Sub ```

Original source