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

0 votes

I want to loop through each page and format the same ranges in each one. I have 10 sheets with the labels "1-10" correspondingly.

The code below runs, but I'm having trouble getting 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
Mar 21, 2023 in Others by narikkadan
• 63,420 points
1,181 views

1 answer to this question.

0 votes

Range(...) instructs VBA to always use the ActiveSheet. You must instruct VBA to work on worksheet ws in order to achieve the desired result: ws.Range (...).

Moreover, Select and Selection are not necessary (and you should avoid using them because they are rarely necessary): Instead, either use a With-clause or assign the Range to a variable and use that:

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
answered Mar 21, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

Create a hyperlink to a sheet with same name as the value in the selected cell in Excel through VBA

Credit to Spectral Instance who found the ...READ MORE

answered Feb 6, 2023 in Others by narikkadan
• 63,420 points
590 views
0 votes
1 answer

How do you populate a google sheets/excel column with cells from a column in another sheet in the same document?

You have two options on chronology: sheet-by-sheet =QUERY({Sheet1!X:Z; Sheet2!X:Z; ...READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 63,420 points
1,180 views
0 votes
1 answer

How to increment the Range of a For Each loop - Excel VBA

Your formula seems to sum 1 single ...READ MORE

answered Jan 7, 2023 in Others by narikkadan
• 63,420 points
2,113 views
0 votes
1 answer

VBA Loop To Import Changing File Names

You can use a FOR loop and ...READ MORE

answered Jan 12, 2023 in Others by narikkadan
• 63,420 points
573 views
0 votes
1 answer

Trying to run different macros based on value of cell in a range

This demonstrates one approach to loop through ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,420 points
277 views
0 votes
1 answer

How to add if this cell = 0 skip and go next

Try this: Option Explicit Sub AutoMakeInv() ...READ MORE

answered Jan 20, 2023 in Others by narikkadan
• 63,420 points
242 views
0 votes
1 answer

Automating production of graphs using VBA

You have to change: chrtname = Cells(i, 2).Value To: chrtname ...READ MORE

answered Jan 23, 2023 in Others by narikkadan
• 63,420 points
446 views
0 votes
1 answer

Loop through each cell in a given range and change the value depending on value in a column in the same row

Use match() and if() without code at ...READ MORE

answered Mar 26, 2023 in Others by Kithuzzz
• 38,010 points
534 views
0 votes
1 answer

Excel VBA: Obtain the Column and Row of current element in "For Each" loop

Try this: MsgBox rng.Address(RowAbsolute:=False, ColumnAbsolute:=F ...READ MORE

answered Feb 14, 2023 in Others by Kithuzzz
• 38,010 points
777 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP