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
Which line is the error on, I suspect array Academy is not defined
Yes, I changed "Academy" for "study" and it is now working but only on the active worksheet I have on the screen. It is not working on the others for some reason.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
250
Office Version
  1. 2007
Platform
  1. Windows
Hello Dannottheman,
I don't understand why you don't create one procedure?
Something like this...
VBA Code:
Option Explicit

Sub Dannottheman3()

    Dim Cl As Range
    Dim srch As Variant
    Dim varWS As Worksheet
    
    Application.ScreenUpdating = False
    For Each varWS In Worksheets
        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
        
        With varWS.Columns("A")
            .ColumnWidth = 95
            .WrapText = True
        End With
        
        varWS.Columns("A:B").NumberFormat = "General"
        varWS.Cells(1, 1).EntireRow.Insert
        Debug.Print "| &"; varWS.Name; "& |", Len(varWS.Name)
        
        With varWS.Columns("A")
             .Columns(1).ClearFormats
        End With
    Next
    Application.ScreenUpdating = True
    
End Sub
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,572
Office Version
  1. 2010
Platform
  1. Windows
Yes, I changed "Academy" for "study" and it is now working but only on the active worksheet I have on the screen. It is not working on the others for some reason.
add this line to your sub routine:
VBA Code:
Sub test()
Dim wb As Workbook
Dim ws As Worksheet
For Each wb In Application.Workbooks
If wb.Name <> "PERSONAL.XLSB" Then
For Each ws In wb.Sheets
ws.Select               ' add this line, not the fastest way of doing this but only one line to change
Call Dannottheman3
Next ws
End If
Next wb
End Sub
 

Dannottheman

Board Regular
Joined
Dec 24, 2020
Messages
55
Office Version
  1. 2007
Hello Dannottheman,
I don't understand why you don't create one procedure?
Something like this...
VBA Code:
Option Explicit

Sub Dannottheman3()

    Dim Cl As Range
    Dim srch As Variant
    Dim varWS As Worksheet
   
    Application.ScreenUpdating = False
    For Each varWS In Worksheets
        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
       
        With varWS.Columns("A")
            .ColumnWidth = 95
            .WrapText = True
        End With
       
        varWS.Columns("A:B").NumberFormat = "General"
        varWS.Cells(1, 1).EntireRow.Insert
        Debug.Print "| &"; varWS.Name; "& |", Len(varWS.Name)
       
        With varWS.Columns("A")
             .Columns(1).ClearFormats
        End With
    Next
    Application.ScreenUpdating = True
   
End Sub
Thanks, this partially works. The first procedure (Cl.Interior.Color = rgbLightBlue) does not work/execute. I get no error, it just doesn't shade the sheet (I am running Excel 2007).
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
250
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Give some data table example, and we can reorder code.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,007
Office Version
  1. 365
Platform
  1. Windows
it just doesn't shade the sheet (I am running Excel 2007).
It probably does, but it's then removed by this part of the code.
VBA Code:
        With varWS.Columns("A")
             .Columns(1).ClearFormats
        End With
 

Dannottheman

Board Regular
Joined
Dec 24, 2020
Messages
55
Office Version
  1. 2007
It probably does, but it's then removed by this part of the code.
VBA Code:
        With varWS.Columns("A")
             .Columns(1).ClearFormats
        End With
Yes, you are right! OK. I think this is almost working. I need to add this step to the bottom of the code (add row after two other steps have occurred). I tried different combinations but I keep getting different types of errors. Thanks in advance:

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

(Working code below)

Option Explicit

Sub Dannottheman3()

Dim Cl As Range
Dim srch As Variant
Dim varWS As Worksheet

Application.ScreenUpdating = False
For Each varWS In Worksheets
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

With varWS.Columns("A")
.ColumnWidth = 95
.WrapText = True
End With
Next
Application.ScreenUpdating = True

End Sub
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
250
Office Version
  1. 2007
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,022
Messages
5,575,625
Members
412,680
Latest member
TSpan
Top