Excel VBA For Each Worksheet Loop
Solution 1
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)
Solution 2
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.
Solution 3
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
Related videos on Youtube
Dakota
Updated on February 14, 2020Comments
-
Dakota about 4 years
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
-
user2140261 about 10 yearsnow 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.
-
-
Jacob about 10 years@simoco posted a more concise version of my answer. Should I remove mine?
-
Dmitry Pavliv about 10 yearsanother way is to use
Array(20.14,9.71,..)
instead string+split:) -
user2140261 about 10 yearsShorter code but will use more memory and be slightly slower. Shorter doesn't mean better.
-
WitchGod about 10 yearsYep, 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. :) -
Luuklag about 6 yearsI 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.