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

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,548
Office Version
  1. 2010
Platform
  1. Windows
try this:
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
   Call vba1
   'etc
   Next ws
 End If
Next wb
End Sub
 

Dannottheman

Board Regular
Joined
Dec 24, 2020
Messages
55
Office Version
  1. 2007
try this:
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
   Call vba1
   'etc
   Next ws
End If
Next wb
End Sub
It give an error "Compilation error" Sub or Function not defined
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,548
Office Version
  1. 2010
Platform
  1. Windows
You need to change
VBA Code:
Call VBA1
to what ever the name is of the sub routines you want to run e.g test3 ()
 

Dannottheman

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

ADVERTISEMENT

I did but it doesn't work.
You need to change
VBA Code:
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...
 

Attachments

  • error.png
    error.png
    24.1 KB · Views: 7

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,548
Office Version
  1. 2010
Platform
  1. Windows
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:
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
 MsgBox (wb.Name & " / " & ws.Name)
     'etc
   Next ws
 End If
Next wb
End Sub
 

eduzs

Well-known Member
Joined
Jul 6, 2014
Messages
634
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Dannottheman

Board Regular
Joined
Dec 24, 2020
Messages
55
Office Version
  1. 2007
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:
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
MsgBox (wb.Name & " / " & ws.Name)
     'etc
   Next ws
End If
Next wb
End Sub
Please see attached image
 

Attachments

  • error2.png
    error2.png
    24.7 KB · Views: 4

Dannottheman

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

Well-known Member
Joined
Dec 23, 2017
Messages
1,548
Office Version
  1. 2010
Platform
  1. Windows
Which line is the error on, I suspect array Academy is not defined
 

Watch MrExcel Video

Forum statistics

Threads
1,118,176
Messages
5,570,690
Members
412,336
Latest member
Tiffany927
Top