Run VBA on all open tabs

Dannottheman

Board Regular
Joined
Dec 24, 2020
Messages
55
Office Version
  1. 2007
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

Board Regular
Joined
Dec 24, 2020
Messages
55
Office Version
  1. 2007
Is now better order of the steps?

VBA Code:
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 Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
392
Office Version
  1. 2016
Platform
  1. Windows
A little modification...

VBA Code:
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

Board Regular
Joined
Dec 24, 2020
Messages
55
Office Version
  1. 2007
A little modification...

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,126
Messages
5,640,270
Members
417,132
Latest member
Srw123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top