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
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)
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.
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.
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
Source: Stackoverflow.com