Quick Question about applying macro to multiple sheets

[sale_MB]

Is there a way to run the codes below on every sheet in my workbook? I have 50+ sheets (tab1, tab2, tab3... tab50...) I musto to change column B and X to upper case and after do action find and replace. I using 2 macros, 1st is for change case to upper case, 2nd for find EXAMPLE 1 and replace with EXAPLE 2.

<code style="margin: 0px; padding: 0px; font-style: inherit;">Sub vs()
Dim Rng As Range
On Error Resume Next
Err.Clear
Application.EnableEvents = False
For Each Rng In Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues).Cells
If Err.Number = 0 Then
Rng.Value = StrConv(Rng.Text, vbUpperCase)
' Rng.Value = StrConv(Rng.Text, vbLowerCase)
' Rng.Value = StrConv(Rng.Text, vbProperCase)
End If
Next Rng
Application.EnableEvents = True


End Sub

******AND SECOND MACRO****

ublic Sub Priority()
Dim s as String
Dim cell as Range
For Each cell In Selection
s = cell.Text
select case s


case "R. FC BARCELONA"
Cell.Value = "BARCELONA"
case "VALENCIA B.C."
Cell.Value = "PAMESA"
case "GRAN CANARIA 2014"
Cell.Value = "GRAN CANARIA"
case "B. BILBAO BASKET"
Cell.Value = "BILBAO"

End Select
Next cell
End Sub

</code>

 

[pboltonchina]

Try this

For Each ws In Sheets
ws.Activate
'Your code
Next ws

 

[AlphaFrog]

If you are making the text upper case to do the priority replace, that is not required. This uses the .Replace method on each sheet. It can be case insensitive.

[color=darkblue]Sub[/color] Priority_All_Sheets()
    
    [color=darkblue]Dim[/color] ws     [color=darkblue]As[/color] Worksheet
    
    Application.ScreenUpdating = [color=darkblue]False[/color]
    Application.EnableEvents = [color=darkblue]False[/color]
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] ws [color=darkblue]In[/color] Worksheets
        [color=darkblue]With[/color] ws.Range("B:B, X:X")
            
            [color=green]'Priority[/color]
            .Replace "R. FC BARCELONA", "BARCELONA", xlWhole, , [color=darkblue]False[/color]
            .Replace "VALENCIA B.C.", "PAMESA", xlWhole, , [color=darkblue]False[/color]
            .Replace "GRAN CANARIA 2014", "GRAN CANARIA", xlWhole, , [color=darkblue]False[/color]
            .Replace "BILBAO BASKET", "BILBAO", xlWhole, , [color=darkblue]False[/color]
            
        [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]Next[/color] ws
    
    Application.EnableEvents = [color=darkblue]True[/color]
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
    MsgBox "Done", , "Replace Priority"
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[sale_MB]

thanks you. You help me very much. God bless you

 

[sale_MB]

again, thanks you. But i need one more code... for change case to UPPERCASE in all sheets because half text is lowercase. I trying to put my code for change case in your code for all sheets but that doesen't work. From now I using your code for find and replace... you help me a lot.

 

[AlphaFrog]

[COLOR=darkblue]Sub[/COLOR] Priority_All_Sheets()
    
    [COLOR=darkblue]Dim[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet, cell [COLOR=darkblue]As[/COLOR] Range
    
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    Application.EnableEvents = [COLOR=darkblue]False[/COLOR]
    
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] ws [COLOR=darkblue]In[/COLOR] Worksheets
        [COLOR=darkblue]With[/COLOR] ws.Range("B:B, X:X")
        
            [B][COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
            [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] cell [COLOR=darkblue]In[/COLOR] .SpecialCells(xlCellTypeConstants, xlTextValues)
                cell.Value = UCase(cell.Value)
            [COLOR=darkblue]Next[/COLOR]
            [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0[/B]
            
            [COLOR=green]'Priority[/COLOR]
            .Replace "R. FC BARCELONA", "BARCELONA", xlWhole, , [COLOR=darkblue]False[/COLOR]
            .Replace "VALENCIA B.C.", "PAMESA", xlWhole, , [COLOR=darkblue]False[/COLOR]
            .Replace "GRAN CANARIA 2014", "GRAN CANARIA", xlWhole, , [COLOR=darkblue]False[/COLOR]
            .Replace "BILBAO BASKET", "BILBAO", xlWhole, , [COLOR=darkblue]False[/COLOR]
            
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=darkblue]Next[/COLOR] ws
    
    Application.EnableEvents = [COLOR=darkblue]True[/COLOR]
    Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
    
    MsgBox "Done", , "Replace Priority"
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]