Skip some worksheets in loop function in Excel VBA

Question

I want to have a button replace the info on MOST sheets with the info from a master sheet when a button is clicked. However, I want it to skip some sheets.

I have the below code that works, but there are 2 sheets I want it to skip when running. How can I specify that is skip the sheets named "Dates" and "Monthly"

 Sub Button4_Click()

 Dim wsVar As Worksheet

 For Each wsVar In ThisWorkbook.Sheets
     With wsVar
        .Range("B9:M30").Value = Worksheets("BASE").Range("B9:M30").Value
     End With
 Next wsVar

 End Sub

teylyn · Accepted Answer · 2021-01-14 00:37:23Z

1

You can use an IF statement to check the name of the worksheet and then act accordingly.

   For Each wsVar In ThisWorkbook.Sheets
     If wsVar.Name = "foo" Or wsVar.Name = "bar" Then
      ' do nothing
     Else
    
        With wsVar
            .Range("B9:M30").Value = Worksheets("BASE").Range("B9:M30").Value
        End With
    
     End If
   Next wsVar

edited Jan 14, 2021 at 0:37

answered Jan 14, 2021 at 0:33

teylyn

36.2k6 gold badges58 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ben H. Over a year ago

Where in my code would the If statement go?

teylyn Over a year ago

I added some sample code. Adjust the foo and bar to the sheets you want to skip.

Ben H. Over a year ago

That's the ticket! Thanks! I'll accept your answer as soon as it lets me.

Ben H. Over a year ago

It has to be a certain amount of time after it's asked

Variatus · Accepted Answer · 2021-01-14 00:52:36Z

This code will exclude all sheets listed in the Exclude array. Note that sheets name comparisons are carried out case-insensitive and leading or trailing blanks are deemed unintentional and removed.

Sub Button4_Click()

    Dim wsVar       As Worksheet
    Dim Exclude()   As String
    Dim i           As Integer
    
    Exclude = Split("Dates,Monthly", ",")
    For Each wsVar In ThisWorkbook.Worksheets
        With wsVar
            For i = UBound(Exclude) To 0 Step -1
                If StrComp(wsVar.Name, Trim(Exclude(i))) = 0 Then Exit For
            Next i
            If i >= 0 Then .Range("B9:M30").Value = Worksheets("BASE").Range("B9:M30").Value
        End With
    Next wsVar

 End Sub

With a small change, you might convert this same function to process only listed sheets. To identify sheets for action positively, rather than negatively, is the safe way, generally speaking. But if you decide to do that, please also do rename the array :-)

Collectives™ on Stack Overflow

Skip some worksheets in loop function in Excel VBA

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related