Loop Any Macro in Multiple Sheets in Same Workbook

[intellitechdataservices]

Hi Everyone, I am using a macro in a single sheet of a workbook. If I want to use same macro in the other sheet of the same workbook then I have to use same macro again. Is there any loop method by which a macro can be run automatically in multiple sheets of the same workbook?

 

[Joe4]

Welcome to the Board!

This link shows you how to loop through different sheets in your workbook: Macro to Loop Through All Worksheets in a Workbook - Microsoft Support
Just be sure to put this code in a General module in VBA, and not in any particular sheet module.
And note that you will either need to qualify all of your range references with the sheet reference, or select/activate the sheet within the loop.

If you run into any issues, post your VBA code, as described here: How to Post Your VBA Code

 

[intellitechdataservices]

Sub ColorMispelledCells()
Dim xRg As Range, xCell As Range
On Error Resume Next
Set xRg = Application.InputBox("please select range：", "KuTools For Excel", Selection.Address, , , , , 8)
If xRg Is Nothing Then Exit Sub
Application.ScreenUpdating = False
For Each xCell In xRg
If Not Application.CheckSpelling(xCell.Text) Then _
xCell.Interior.ColorIndex = 28
Next
Application.ScreenUpdating = True
End Sub

 

[Joe4]

Didn't want to use the code tags for readability, eh?

That VBA code you posted prompts the user to select a range.
Do you want it to select a range for each and every sheet?
If not, how to you want it to work?

 

[intellitechdataservices]

Didn't want to use the code tags for readability, eh?

That VBA code you posted prompts the user to select a range.
Do you want it to select a range for each and every sheet?
If not, how to you want it to work?

No I dont want to select any range.

 

[Joe4]

No I dont want to select any range.

That is an integral part of the code you posted.
If you do not want to select any range, then you need to tell us how you want it to work.
Is there some specific range on every sheet that you want it to run against?
We need to know the requirements for what we are building, since you want to change how it works.

 

[intellitechdataservices]

See in this Wrokbook there are 5 separate sheets names as 2001 2002 2003 2004 and 2005. All these sheets have words in particular columns separated by space delimiter, I want to check error in all the sheets. I used above macro for 1 sheets and spelling mistakes are highlighted in blue color. But i have to run that macro in all sheets separately. So i want to loop the macro for all sheets at once.

 

[Joe4]

OK, I am guessing that you just want to to run against ALL the data on each sheet, regardless of where it ends.
If that is the case, try this:

Sub ColorMispelledCells()

    Dim xRg As Range, xCell As Range
    Dim ws As Worksheet

    On Error Resume Next
    
'   Loop through all sheets
    For Each ws In Worksheets
    
'       Select sheet
        ws.Activate
'       Capture used range on sheet
        Set xRg = ws.UsedRange
    
        If xRg Is Nothing Then Exit Sub
    
        Application.ScreenUpdating = False
        For Each xCell In xRg
            If Not Application.CheckSpelling(xCell.Text) Then _
                xCell.Interior.ColorIndex = 28
        Next xCell

        Application.ScreenUpdating = True
    
    Next ws
    
    MsgBox "Macro complete!"
    
End Sub

 

[intellitechdataservices]

It worked exactly what I wanted!
Thank you for your help!

 

[Joe4]

You are welcome.
Glad I was able to help.