Excel VBA For Each Worksheet Loop

Question

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

now this is running on assumption, and might be wrong but in place of your sub you could use `ws.Columns("A:O").AutoFit` if you thats what you are trying to do. — user2140261, Feb 20 '14 at 20:37

Dmitry Pavliv · Accepted Answer · 2014-02-20T20:09:14.717

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)

score 5 · Answer 2 · answered Feb 20 '14 at 20:14

5

Try this more succinct code:

Sub LoopOverEachColumn()
    Dim WS As Worksheet
    For Each WS In ThisWorkbook.Worksheets
        ResizeColumns WS
    Next WS
End Sub

Private Sub ResizeColumns(WS As Worksheet)
    Dim StrSize As String
    Dim ColIter As Long
    StrSize = "20.14;9.71;35.86;30.57;23.57;21.43;18.43;23.86;27.43;36.71;30.29;31.14;31;41.14;33.86"
    For ColIter = 1 To 15
        WS.Columns(ColIter).ColumnWidth = Split(StrSize, ";")(ColIter - 1)
    Next ColIter
End Sub

If you want additional columns, just change 1 to 15 to 1 to X where X is the column index of the column you want, and append the column size you want to StrSize.

For example, if you want P:P to have a width of 25, just add ;25 to StrSize and change ColIter... to ColIter = 1 to 16.

Hope this helps.

answered Feb 20 '14 at 20:14

WGS

13,969
4
48
51

3

another way is to use `Array(20.14,9.71,..)` instead string+split:) – Dmitry Pavliv Feb 20 '14 at 20:24
2

Shorter code but will use more memory and be slightly slower. Shorter doesn't mean better. – user2140261 Feb 20 '14 at 20:40
1

Yep, that's why I held back on that "claim", lol. However, unless user wants to do this to a couple dozen of worksheets and hundreds of columns, I see updating this as far easier (at least if you know the column index and don't get confused by `StrSize`, harhar). On my end, it does 3 sheets in a relative instant. :) – WGS Feb 20 '14 at 20:43

score 2 · Answer 3 · answered Feb 20 '14 at 20:05

You need to put the worksheet identifier in your range statements as shown below ...

 Option Explicit
 Dim ws As Worksheet, a As Range

Sub forEachWs()

For Each ws In ActiveWorkbook.Worksheets
Call resizingColumns
Next

End Sub

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

@simoco posted a more concise version of my answer. Should I remove mine? — Jacob, Feb 20 '14 at 20:13

score -2 · Answer 4 · answered Feb 13 '18 at 15:43

-2

Instead of adding "ws." before every Range, as suggested above, you can add "ws.activate" before Call instead.

This will get you into the worksheet you want to work on.

answered Feb 13 '18 at 15:43

F.R.

1
2

I would advice against this practice, as it is prone to errors. Explicitly defining the worksheet for each range or command ensures the propper worksheet is used for execution. When using activate a user can manually select another worksheet during execution and your code goes off. Also you better add this as a comment instead of an answer, as it really doesn't answer the question at hand. – Luuklag Feb 13 '18 at 15:47

Excel VBA For Each Worksheet Loop

4 Answers4

Linked

Related