CODEWITHSUNDEEP
vbaexcel
Hazel Popham
Hazel Popham

Reputation: 187

Loop through all worksheets

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

Upvotes: 0

Views: 171

Answers (1)

Kathara
Kathara

Reputation: 1290

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

Upvotes: 1

Related Questions