Format Excel worksheets using for each loop VBA

Question

I have a workbook with 3 sheets. I would like to format each sheet the same using VBA (set font size, auto fit columns, sort). I found a piece of code to loop through the sheets:

Sub wsLoop()

   ' Declare Current as a worksheet object variable.
   Dim ws As Worksheet

   ' Loop through all of the worksheets in the active workbook.
   For Each ws In ActiveWorkbook.Worksheets
    'Code here
   Next ws

End Sub

The original code included a message box that pops up with the sheet name. When inserting the code for formatting that I got from recording a macro, the loop only formatted the first sheet. I have been searching around, but I can't find a simple example. Shouldn't I just be able to drop in:

Cells.Select
With Selection.Font
    .Name = "Calibri"
    .Size = 9
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
End With

Why does this only format the first sheet?

score 1 · Accepted Answer · answered Jun 17 '15 at 16:11

Cells.Select selects the cells of the currently active sheet. This selection doesn't change within the For Each loop

You want to work on the cells of the worksheet provided by the For Each construct, i.e.

Sub wsLoop()
' Declare Current as a worksheet object variable.
Dim ws As Worksheet

    ' Loop through all of the worksheets in the active workbook.
    For Each ws In ActiveWorkbook.Worksheets

        ' format font of currently looped worksheet object feferenced by WS
        With ws.Cells.Font
            .Name = "Calibri"
            .Size = 9
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .TintAndShade = 0
            .ThemeFont = xlThemeFontNone
        End With
    Next ws
End Sub

vacip · Answer 2 · 2015-06-17T17:02:05.270

0

You never tell it to go to another sheet. The easiest and most visual (though not the best!) way would be activating each sheet, then running the code.

Whatever you want to do with each worksheet inside your for each loop, you must do it with the ws. This way you can add any recorded macro to the loop easily.

For Each ws In ActiveWorkbook.Worksheets
 ws.activate
 cells.select
 With selection... yaddayadda
  yaddayadda
 end with
Next ws

Like I said, there are better ways to do this, but since you seem to be a novice, this is the easiest to follow. If you are an advanced programmer, avoid using macro recorder-like coding, as it is slow and ineffective. (Pretty obvious in my opinion, but nvm...)

edited Jun 17 '15 at 17:02

answered Jun 17 '15 at 16:13

vacip

5,246
2
26
54

1

I recommend to consequently avoid `.Activate` and `.Select` and work with objects instead. Code becomes cleaner and faster and screens are not hopping in & out. There are only few cases where `.Activate` and `.Select` are justified. – MikeD Jun 17 '15 at 16:21
1

http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros provides indepth explanations and good hints ... – MikeD Jun 17 '15 at 16:37
I know, we all know. If you read carefully, I have mentioned this. I have been teaching VBA this for over 10 years now. For novice Excel users trying to get a hang of VBA, using objects is confusing. Using .activeate, .select is the way for them, and later, you can teach them about effective coding. – vacip Jun 17 '15 at 17:00

Format Excel worksheets using for each loop VBA

2 Answers2