Dannottheman
Board Regular
- Joined
- Dec 24, 2020
- Messages
- 55
- Office Version
- 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'>
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'>