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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[excel] Excel VBA For Each Worksheet Loop

Examples related to excel

Examples related to vba

Examples related to for-loop

Examples related to each

Examples related to worksheet