Loop through all worksheets

Question

The code below, rather than looping through all the worksheets (other than the exceptions) executes over and over on the sheet that is active upon execution.

What have I done wrong?

Dim wsSheet As Worksheet

  For Each wsSheet In ThisWorkbook.Worksheets
    Select Case wsSheet.Name
        Case "Affiliates", "New Report", "Pasted Report", "New Month Or Client", "Set Up Data"
            'Do nothing.

        Case Else
            With wsSheet
  Range("B72").Select
  Range("B72:L86").Sort Key1:=Range("I72"), Order1:=xlDescending, Header:= _
  xlGuess, OrderCustom:=1, MatchCase:=False, _
  Orientation:=xlTopToBottom, _
  DataOption1:=xlSortNormal

  Range("B72:L72,B74:L74,B76:L76,B78:L78,B80:L80,B82:L82,B84:L84,B86:L86").Select
  Range("B86").Activate
  ExecuteExcel4Macro "PATTERNS(,0,1,TRUE,2,4,0,0)"
  Range("B73:L73,B75:L75,B77:L77,B79:L79,B81:L81,B83:L83,B85:L85").Select
  Range("B85").Activate
  ExecuteExcel4Macro "PATTERNS(,0,10,TRUE,2,4,0,0.799981688894314)"
  Range("C93").Select

            End With
    End Select

  Next wsSheet

End Sub

It is looping but when using the `With` block one must put a `.` before those objects/functions that should be qualified by the `With`. Son in front of all your `Ranges` put a `.` like `.Range(...)` — Scott Craner, Jan 08 '16 at 18:10
To piggy back off @Scott's excellent comment - you should always be explicit when using `Range()`, `Cells()`, `Row()`, etc., especially when using multiple spreadsheets. Using `With` is a help, since you just need the placeholding `.`. Just noting that you should use this always, so you could even have like `.Range(.cells(2,1),.Cells(.Rows.Count,2)) ...` — BruceWayne, Jan 08 '16 at 18:13
I've put a `.` in front of all the `Ranges` but now I'm getting - Run-time error '1004': Method 'Select' of object 'Range' failed — Hazel Popham, Jan 08 '16 at 18:41
That's because you're trying to select a range on a worksheet that is not the `ActiveSheet`. Technically, you could correct this by activating or selecting the worksheet before trying to select any of the ranges, but I *highly* recommend avoiding `Select` statements in your macros: http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros — tigeravatar, Jan 08 '16 at 20:00
If you want to select ranges in the `wsSheet` you would also have to have the sheet selected as well, or it will just select the range of the sheet you are running the code from, or you will get an error as you have discovered. `.select` is required before `.Range("B72").Select` — Davesexcel, Jan 08 '16 at 22:13
add wsSheet.Activate as first line within your with statememnt — Chicago Excel User, Jan 08 '16 at 22:16

score 1 · Accepted Answer · answered Jan 11 '16 at 14:29

Your code should look something like this, when corrected:

Dim wsSheet As Worksheet

For Each wsSheet In ThisWorkbook.Worksheets
    Select Case wsSheet.Name
        Case "Affiliates", "New Report", "Pasted Report", "New Month Or Client", "Set Up Data"
            'Do nothing.
        Case Else
            wsSheet.Select
            With ActiveWorksheet
                .Range("B72").Select
                .Range("B72:L86").Sort Key1:=Range("I72"), Order1:=xlDescending, Header:= _
                    xlGuess, OrderCustom:=1, MatchCase:=False, _
                    Orientation:=xlTopToBottom, _
                    DataOption1:=xlSortNormal

                .Range("B72:L72,B74:L74,B76:L76,B78:L78,B80:L80,B82:L82,B84:L84,B86:L86").Select
                .Range("B86").Activate
                ExecuteExcel4Macro "PATTERNS(,0,1,TRUE,2,4,0,0)"
                .Range("B73:L73,B75:L75,B77:L77,B79:L79,B81:L81,B83:L83,B85:L85").Select
                .Range("B85").Activate
                ExecuteExcel4Macro "PATTERNS(,0,10,TRUE,2,4,0,0.799981688894314)"
                .Range("C93").Select
            End With
    End Select
Next wsSheet

Loop through all worksheets

1 Answers1