Run VBA on all open tabs

[Dannottheman]

Hi,

I searched but could not find an answer to this. How can I make the 3 VBAs below run on all the open sheets/tabs in my Excel? Right now I have to manually click on each each sheet and "execute", "execute", "execute", etc...
Ideally, I would have 20 open spreadsheets and the VBAs would apply to all of them. Right now I have to click at least 100 to 150 times to make the changes in 20 sheets when in reality it would be great to click just 5 to 10 times total. Thank you in advance as this task is stressing me out and I have hundreds of these sheets.

VBA 1:
class=prism-token>Option Explicit
Sub Dannottheman3()
Dim Cl As Range
Dim srch As Variant
For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each srch In Array("quiz", "unit", "exam", class=prism-token>"examen", "assessment", "test") ' <= note the different use of the jolly (could be useful)
If " " & LCase(Cl.Value) & " " Like "*[!a-z0-9]" & srch & "[!a-z0-9]*" Then
Cl.Interior.Color = rgbLightBlue
Cl.Font.Bold = True
Exit For
End If
Next srch
Next Cl
End Sub

VBA 2:
Sub Test3()
Application.ScreenUpdating = False
With ActiveSheet.Columns("A")
.ColumnWidth = 95
.WrapText = True
End With

VBA 3:
Columns("A:B").NumberFormat = "General"
Application.ScreenUpdating = True
End Sub


VBA 4:
Insert Row
Sub testit2()
Worksheets(1).Cells(1, 1).EntireRow.Insert
Debug.Print "| &"; Worksheets(1).Name; "& |", Len(Worksheets(1).Name)
End Sub


VBA 5:
Clear format:
Sub clearformat()
With ActiveSheet.Columns("A")
.Columns(1).ClearFormats 'clear formatting from column A
End With
End Substyle='font-size:9.0pt;font-family:Consolas'>

 

[Dannottheman]

Is now better order of the steps?

Sub Dannottheman3()

    Dim Cl As Range
    Dim srch As Variant
    Dim varWS As Worksheet
   
    Application.ScreenUpdating = False
    For Each varWS In Worksheets
        With varWS.Columns("A")
            .Columns(1).ClearFormats
            .ColumnWidth = 95
            .WrapText = True
        End With
        varWS.Columns("A:B").NumberFormat = "General"
        varWS.Cells(1, 1).EntireRow.Insert
        For Each Cl In varWS.Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
            For Each srch In Array("quiz", "unit", "exam", "examen", "assessment", "test")
                If " " & LCase(Cl.Value) & " " Like "*[!a-z0-9]" & srch & "[!a-z0-9]*" Then
                    Cl.Interior.Color = rgbLightBlue
                    Cl.Font.Bold = True
                    Exit For
                End If
            Next srch
        Next Cl
        Debug.Print "| &"; varWS.Name; "& |", Len(varWS.Name)
    Next
    Application.ScreenUpdating = True
   
End Sub

Yes, this works! Do you know how to add this to the code so that it also runs altogether? I tried several ways and couldn't get it to work. Ideally, I would want to add the word "Test" to the new row and color it (Cl.Interior.Color = rgbLightBlue)

Sub testit2()
Worksheets(1).Cells(1, 1).EntireRow.Insert
Debug.Print "| &"; Worksheets(1).Name; "& |", Len(Worksheets(1).Name)
End Sub

 

[EXCEL MAX]

A little modification...

Sub Dannottheman3()

    Dim Cl As Range
    Dim srch As Variant
    Dim varWS As Worksheet
    
    Application.ScreenUpdating = False
    For Each varWS In Worksheets
         With varWS.Columns("A")
            .ClearFormats
            .ColumnWidth = 95
            .WrapText = True
            .Cells(1, 1).EntireRow.Insert
            .Cells(1, 1).Interior.Color = rgbLightBlue
            .Cells(1, 1).Value = "Test"
        End With
        varWS.Columns("A:B").NumberFormat = "General"
        For Each Cl In varWS.Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
            For Each srch In Array("quiz", "unit", "exam", "examen", "assessment", "test")
                If " " & LCase(Cl.Value) & " " Like "*[!a-z0-9]" & srch & "[!a-z0-9]*" Then
                    Cl.Interior.Color = rgbLightBlue
                    Cl.Font.Bold = True
                    Exit For
                End If
            Next srch
        Next Cl
        Debug.Print "| &"; varWS.Name; "& |", Len(varWS.Name)
    Next
    Application.ScreenUpdating = True
    
End Sub

 

[Dannottheman]

A little modification...

Sub Dannottheman3()

    Dim Cl As Range
    Dim srch As Variant
    Dim varWS As Worksheet
   
    Application.ScreenUpdating = False
    For Each varWS In Worksheets
         With varWS.Columns("A")
            .ClearFormats
            .ColumnWidth = 95
            .WrapText = True
            .Cells(1, 1).EntireRow.Insert
            .Cells(1, 1).Interior.Color = rgbLightBlue
            .Cells(1, 1).Value = "Test"
        End With
        varWS.Columns("A:B").NumberFormat = "General"
        For Each Cl In varWS.Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
            For Each srch In Array("quiz", "unit", "exam", "examen", "assessment", "test")
                If " " & LCase(Cl.Value) & " " Like "*[!a-z0-9]" & srch & "[!a-z0-9]*" Then
                    Cl.Interior.Color = rgbLightBlue
                    Cl.Font.Bold = True
                    Exit For
                End If
            Next srch
        Next Cl
        Debug.Print "| &"; varWS.Name; "& |", Len(varWS.Name)
    Next
    Application.ScreenUpdating = True
   
End Sub

Thanks! Do you know how to add this to the code below so that it also runs together as one single VBA? I tried several ways and couldn't get it to work. Ideally, I would want to add the word "Test" to the new row and color it (Cl.Interior.Color = rgbLightBlue)

Sub testit2()
Worksheets(1).Cells(1, 1).EntireRow.Insert
Debug.Print "| &"; Worksheets(1).Name; "& |", Len(Worksheets(1).Name)
End Sub