Excel VBA- How to loop through specific sheets in a workbook and format the same ranges in each sheet

I have 10 sheets labeled 1-10 respectively and I want to loop through each sheet and format the same ranges in each sheet.

The below code works but I cannot get it to loop through each sheet

Sub Macro1()
'

Dim wsList() As String, wsName As Variant, ws As Worksheet
   
wsList = Split("1 2 3 4 5 6 7 8 9 10", " ")
    
For Each wsName In wsList
    Set ws = ThisWorkbook.Sheets(wsName)
      
    Range("A4:C7,L4:Q5,L6:L7,S24:U27,AD24:AF27,AO28:AR29,AO26:AO27").Select
    
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    
    Range("B4:C7,M4:Q5,T24:U27,AE24:AF27,AP28:AR29").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    
    Range("O31:AF34").Select
    ActiveCell.FormulaR1C1 = "FALL"
    Range("O35").Select
    
Next wsName

End Sub

</details>


# 答案1
**得分**: 1

Here's the translated content:

如果你写 `Range(...)`，VBA 将始终使用 `ActiveSheet`。你想要的是它在工作表 `ws` 上运行，因此你必须告诉 VBA：`ws.Range(...)`。

另外，没有必要使用 `Select` 和 `Selection`（你应该避免使用它，几乎从不需要）：相反，要么将 Range 分配给一个变量并使用它，要么使用 `With` 子句：

Dim wsList() As String, wsName As Variant, ws As Worksheet

wsList = Split("1 2 3 4 5 6 7 8 9 10", " ")

For Each wsName In wsList
Set ws = ThisWorkbook.Sheets(wsName)

' 选项 a) 使用变量
Dim myRange as Range
Set myRange = ws.Range("A4:C7,L4:Q5,L6:L7,S24:U27,AD24:AF27,AO28:AR29,AO26:AO27")

With myRange.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
' 或者，不使用 With
myRange.Interior.Pattern = xlNone
myRange.Interior.TintAndShade = 0
myRange.Interior.PatternTintAndShade = 0

' 选项 b) 直接使用中间变量的 With
With ws.Range("B4:C7,M4:Q5,T24:U27,AE24:AF27,AP28:AR29")
    .Locked = False
    .FormulaHidden = False
End With

ws.Range("O31:AF34").FormulaR1C1 = "FALL"

Next wsName

顺便说一下，你的关于范围 "O31:AF34" 的公式看起来奇怪。这不是一个有效的公式。如果你想将文本 ""FALL"" 写入单元格，请使用 `ws.Range("O31:AF34").Value = "FALL"`。

<details>
<summary>英文:</summary>

If you write `Range(...)`, VBA will always use the `ActiveSheet`. What you want is that is works on worksheet `ws`, so you have to tell VBA it: `ws.Range(...)`.

Also, there is no need to use `Select` and `Selection` (and you should avoid it, it is almost never needed): Instead, either assign the Range to a variable and use that, or use a `With`-clause:

Dim wsList() As String, wsName As Variant, ws As Worksheet

wsList = Split("1 2 3 4 5 6 7 8 9 10", " ")

For Each wsName In wsList
    Set ws = ThisWorkbook.Sheets(wsName)
      
    ' Option a) Use a Variable
    Dim myRange as Range
    Set myRange = ws.Range("A4:C7,L4:Q5,L6:L7,S24:U27,AD24:AF27,AO28:AR29,AO26:AO27")
    
    With myRange.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    ' Or, without With
    myRange.Interior.Pattern = xlNone
    myRange.Interior.TintAndShade = 0
    myRange.Interior.PatternTintAndShade = 0

    ' Option b) Use With directly with intermediate variable
    With ws.Range("B4:C7,M4:Q5,T24:U27,AE24:AF27,AP28:AR29")
        .Locked = False
        .FormulaHidden = False
    End With

    ws.Range("O31:AF34").FormulaR1C1 = "FALL"
Next wsName

Your formula for the range "O31:AF34" looks odd, btw. This is not a valid formula. If you want to write the text `"FALL"` into the cell, use `ws.Range("O31:AF34").Value = "FALL"`

</details>