Excel VBA For Each Worksheet Loop

Try to slightly modify your code:

Sub forEachWs()    Dim ws As Worksheet    For Each ws In ActiveWorkbook.Worksheets        Call resizingColumns(ws)    NextEnd SubSub 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 WithEnd 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)

Try this more succinct code:

Sub LoopOverEachColumn()    Dim WS As Worksheet    For Each WS In ThisWorkbook.Worksheets        ResizeColumns WS    Next WSEnd SubPrivate 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 ColIterEnd 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 RangeSub forEachWs()For Each ws In ActiveWorkbook.WorksheetsCall resizingColumnsNextEnd SubSub resizingColumns()ws.Range("A:A").ColumnWidth = 20.14ws.Range("B:B").ColumnWidth = 9.71ws.Range("C:C").ColumnWidth = 35.86ws.Range("D:D").ColumnWidth = 30.57ws.Range("E:E").ColumnWidth = 23.57ws.Range("F:F").ColumnWidth = 21.43ws.Range("G:G").ColumnWidth = 18.43ws.Range("H:H").ColumnWidth = 23.86ws.Range("i:I").ColumnWidth = 27.43ws.Range("J:J").ColumnWidth = 36.71ws.Range("K:K").ColumnWidth = 30.29ws.Range("L:L").ColumnWidth = 31.14ws.Range("M:M").ColumnWidth = 31ws.Range("N:N").ColumnWidth = 41.14ws.Range("O:O").ColumnWidth = 33.86End Sub