Apply code to all but one worksheet in workbook

[linesite]

I create a daily summary report each day that includes a pivot table with several worksheets based on filter pages within the pivot table. The pivot table includes a worksheet called "Executive Speeding Report" and "PT". Essentially the Executive Speeding Report worksheet contains the "raw" data while the "PT" worksheet is the pivot table portion of the workbook. The filter pages contain details exclusively to each division impacted by the summary report--these worksheets are labeled "Div. 041", "Div. 062", "Div. 089" based on driving behavior the previous day (see attached image). This daily workbook could contain as many as 30 worksheets or as few as 1 during each daily download.

Can someone help me add VBA to apply this conditional formatting macro to each worksheet in the workbook except the worksheet labeled "Executive Speeding Report"?

Dim myWB As Workbook
Dim myWS As Worksheet
Dim FirstCell, myRows, myColumns, myUsedRange As Range

Set myWB = ActiveWorkbook
Set myWS = myWB.ActiveSheet
Set FirstCell = myWS.Cells(5, 1)
Set myRows = myWS.Cells(Rows.Count, 1).End(xlUp).Rows
Set myColumns = myWS.Cells(5, Columns.Count).End(xlToLeft).Columns
Set myUsedRange = myWS.Range(myRows, myColumns)

Application.ScreenUpdating = False

myUsedRange.Select

myUsedRange.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(TEXT($D5,""hh:mm:ss"")>""00:01:01"",OR(ISNUMBER(SEARCH(""6-"",$E5)),ISNUMBER(SEARCH(""7-"",$E5)),ISNUMBER(SEARCH(""9-"",$E5))),NUMBERVALUE(TEXT($G5,""##""))>63)"

With Selection.FormatConditions(1).Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With

With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With

Cells.Select
Cells.EntireColumn.AutoFit

Application.ScreenUpdating = True

Range("A1").Select

End Sub

 

[jasonb75]

When you post code, please click the </> icon and paste it into the popup window.

A lot could be done to clean up your code, but for what you're asking, replace the line Set myWS = myWB.ActiveSheet with these 2 lines

For Each myWS in myWB.WorkSheets
    If myWS.Name <>"Executive Speeding Report" Then

and insert these 2 lines just before End Sub

    End If
Next

It might need a couple of other adjustments, it's easy to miss things in unformatted code.

 

[linesite]

I made adjustments based on your response--is this correct?

Sub Test_Add_CF_PT()

Dim myWB                                                        As Workbook
Dim myWS                                                        As Worksheet
Dim FirstCell, myRows, myColumns, myUsedRange                   As Range

Set myWB = ActiveWorkbook
Set myWS = myWB.ActiveSheet
Set FirstCell = myWS.Cells(5, 1)
Set myRows = myWS.Cells(Rows.Count, 1).End(xlUp).Rows
Set myColumns = myWS.Cells(5, Columns.Count).End(xlToLeft).Columns
Set myUsedRange = myWS.Range(myRows, myColumns)

Application.ScreenUpdating = False

For Each myWS In myWB.Worksheets
    If myWS.Name <> "Executive Speeding Report" Then

    myUsedRange.Select
      
    myUsedRange.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(TEXT($D5,""hh:mm:ss"")>""00:01:01"",OR(ISNUMBER(SEARCH(""6-"",$E5)),ISNUMBER(SEARCH(""7-"",$E5)),ISNUMBER(SEARCH(""9-"",$E5))),NUMBERVALUE(TEXT($G5,""##""))>63)"
            
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
        
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
        
    Cells.Select
    Cells.EntireColumn.AutoFit
    
    Application.ScreenUpdating = True
        
    Range("A1").Select

End If

Next
    
End Sub

 

[jasonb75]

I made adjustments based on your response--is this correct?

Doesn't look like it,

replace the line Set myWS = myWB.ActiveSheet with these 2 lines

 

[linesite]

I made this adjustment to the code. The macro appears to run through and applies conditional formatting but it does not autofit columns.

Sub Test_Add_CF_PT()

Dim myWB                                                        As Workbook
Dim myWS                                                        As Worksheet
Dim FirstCell, myRows, myColumns, myUsedRange                   As Range

Set myWB = ActiveWorkbook
'Set myWS = myWB.ActiveSheet

For Each myWS In myWB.Worksheets
    If myWS.Name <> "Executive Speeding Report" Then
    
Set FirstCell = myWS.Cells(5, 1)
Set myRows = myWS.Cells(Rows.Count, 1).End(xlUp).Rows
Set myColumns = myWS.Cells(5, Columns.Count).End(xlToLeft).Columns
Set myUsedRange = myWS.Range(myRows, myColumns)

'Application.ScreenUpdating = False

'For Each myWS In myWB.Worksheets
'    If myWS.Name <> "Executive Speeding Report" Then

'    myUsedRange.Select
      
    myUsedRange.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(TEXT($D5,""hh:mm:ss"")>""00:01:01"",OR(ISNUMBER(SEARCH(""6-"",$E5)),ISNUMBER(SEARCH(""7-"",$E5)),ISNUMBER(SEARCH(""9-"",$E5))),NUMBERVALUE(TEXT($G5,""##""))>63)"
            
    With myUsedRange.FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
        
    With myUsedRange.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
        
'    Cells.Select
    Cells.EntireColumn.AutoFit
    
'    Application.ScreenUpdating = True
        
    Range("A1").Select

End If

Next
    
End Sub

 

[jasonb75]

Here's your code cleaned up a bit, the formula for the cf rule looks as though it could be simplified as well but I've left that for now.

Sub Test_Add_CF_PT()

Dim myWB As Workbook
Dim myWS As Worksheet
Dim myRows As Range, myColumn As Range, myUsedRange As Range
   
    Application.ScreenUpdating = False
    Set myWB = ActiveWorkbook

For Each myWS In myWB.Worksheets
    If myWS.Name <> "Executive Speeding Report" Then
        Set myRows = myWS.Cells(Rows.Count, 1).End(xlUp).Rows
        Set myColumns = myWS.Cells(5, Columns.Count).End(xlToLeft).Columns
        Set myUsedRange = myWS.Range(myRows, myColumns)
     
        With myUsedRange
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=AND(TEXT($D5,""hh:mm:ss"")>""00:01:01"",OR(ISNUMBER(SEARCH(""6-"",$E5)),ISNUMBER(SEARCH(""7-"",$E5)),ISNUMBER(SEARCH(""9-"",$E5))),NUMBERVALUE(TEXT($G5,""##""))>63)"
           
            With Selection.FormatConditions(1)
                With .Font
                    .ThemeColor = xlThemeColorDark1
                    .TintAndShade = 0
                End With
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .Color = 255
                    .TintAndShade = 0
                End With
            End With
        End With
        .Cells.EntireColumn.AutoFit
    End If
Next
    Application.ScreenUpdating = True
End Sub

edit:- the autofit problem that you encountered was as a result of removing the Select / Selection lines from the code. If you compare your version to mine, you will see the dot / period at the start of the autofit line, without this the autofit is being applied to the last selected sheet rather than the current sheet set by myWS.
Also, check the declarations at the top, (Dim ??? As ????), in the last line of your Dim's only myUsedRange is actually a range, the rest are variants.

 

[linesite]

jasonb75--it worked. Your code recommendations and modifications will save me a bunch of time every day working with these reports. Thank you very much for your expertise and time!