Why wont VBA Next go to next Worksheet

Question

So I've been looking on here for a while and I'm trying to make code that goes through every sheet in the workbook unless the sheets are named Summary, Archive, or Template. When running the code it seems to skip the code when I start on a sheet with any of these three names but never goes to the next sheet. (It may be important to mention that my code involves going to another workbook to gather data).

Here's what I have:

For Each rs In ActiveWorkbook.Worksheets

If rs.Name <> "Summary" And rs.Name <> "Archive" And rs.Name <> "Template" Then


    'my Code to do


End If
Next rs

Any help would be greatly appreciated. Thanks

What do you mean by "start on a sheet"? What is triggering this code to run? Is there a chance that the wrong workbook might be active when you define your list of worksheets for iteration? — Nathan Clement, Apr 03 '17 at 23:40

score 2 · Answer 1 · 2017-04-04T00:07:04.317

2

You are using Activesheet or no parent worksheet at all in the actual processing code that you omitted from your question. Use rs instead.

For Each rs In ActiveWorkbook.Worksheets
    WITH RS    '<~~ use rs
        select case lcase(.Name)
            case "summary", "archive", "template"
                'do nothing
            case else
                rs.Select    '<~~ not considered 'best practice'
                'my Code to do with stuff .Range("A1") or .Cells(1, "A") on rs
        end select
     END WITH
Next rs

Notice that when in a With ... End With you precede all the worksheet members with a . that pushes the parent worksheet to the one described in the With ... End With.

edited Apr 04 '17 at 00:07

answered Apr 03 '17 at 23:48

I tried your code and ran into a similar problem I was having with the original. My first 3 sheets are Summary, Archive, and Template. Running the code it seems to skip the 'case else', section three times and then starts running the code. But each time it runs "Next rs" it stays on the same worksheet and doesn't actually go to the next sheet in the workbook. – Ryan S Apr 03 '17 at 23:55
You do not WANT it to 'go' to the next worksheet. Yopu should be able to do anything you want with direct parent worksheet reference. See [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). FWIW, I've added a `rs.Select` command for now but this is **not** considered 'best practice'. – Apr 04 '17 at 00:06
1

Did you read the part about '*Notice that when in a With ... End With you precede all the worksheet members with a . that pushes the parent worksheet to the one described in the With ... End With.*'. – Apr 04 '17 at 00:07
Unfortunately I am still very new to VBA and don't have a great grasp yet on terms and commands (first time I've heard of select case), and I'm unsure what you mean by parent worksheet. I guess what I want, is if the sheet is named summary, archive, or template go to the next sheet. If it isn't named any of those 3, do the code and then go to the next sheet until it loops through all sheets in the workbook – Ryan S Apr 04 '17 at 00:29

score 0 · Answer 2 · answered Apr 04 '17 at 02:56

When you meet something unexpected like what you describe, I find it is best to get a visual check of what's going on. Try this:-

Sub Ryan_S()

    Dim Ws As Worksheet

    For Each Ws In ActiveWorkbook.Worksheets

        Debug.Print Ws.Name

        If Ws.Name <> "Summary" And Ws.Name <> "Archive" And Ws.Name <> "Template" Then

        Debug.Print , "Executing my code"
        'my Code to do

        End If
    Next Ws
End Sub

Except for my little revolt against calling a Worksheet "rs" (which would be a row in my book :-)) the code is exactly like yours. However, I added two lines of code which print something to the Immediate window (Ctl+G if it isn't visible by default). The first line just prints the name of each sheet being called, the second prints only on one of the selected sheets.

This test will guide you toward the error. You might find that you are looping through the wrong workbook (as someone did suggest above), or that your code actually runs but doesn't do what you expect. A common error for beginners is to have On Error Resume Next in it which prevents the code from crashing but doesn't force it to do as instructed.

Why wont VBA Next go to next Worksheet

2 Answers2