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'>

 

[offthelip]

try this:

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
   Call vba1
   'etc
   Next ws
 End If
Next wb
End Sub

 

[Dannottheman]

try this:

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
   Call vba1
   'etc
   Next ws
End If
Next wb
End Sub

It give an error "Compilation error" Sub or Function not defined

 

[offthelip]

You need to change

Call VBA1

to what ever the name is of the sub routines you want to run e.g test3 ()

 

[Dannottheman]

I did but it doesn't work.

You need to change

Call VBA1

to what ever the name is of the sub routines you want to run e.g test3 ()

I did. Still shows the same error...

 

[offthelip]

try running this which should show you that the loop is correctly selecting each of the worksheets in each of the workbooks. If you are getting the sub undefined error it is something to do with where you are putting the subrouitned:

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
 MsgBox (wb.Name & " / " & ws.Name)
     'etc
   Next ws
 End If
Next wb
End Sub

 

[eduzs]

There's no dannotheman3 Sub in this module, so there's the error.
Whre's this sub?

 

[Dannottheman]

try running this which should show you that the loop is correctly selecting each of the worksheets in each of the workbooks. If you are getting the sub undefined error it is something to do with where you are putting the subrouitned:

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
MsgBox (wb.Name & " / " & ws.Name)
     'etc
   Next ws
End If
Next wb
End Sub

Please see attached image

 

[Dannottheman]

There's no dannotheman3 Sub in this module, so there's the error.
Whre's this sub?

I tried placing the dannottheman2 Sub in different places of the module and it would still show the same error. For example:

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
Call Dannottheman3
Next ws
End If
Next wb
End Sub

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("Academy") ' <= 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

 

[offthelip]

Which line is the error on, I suspect array Academy is not defined