Run the same Macro on All WorkSheets

[edmore]

Hello

I would like to run this code on all worksheets. Currently, it works well on one sheet.

How do I change the code?

Many Thanks

Sub Sort_by_colour()
'
' Sort_by_colour Macro
'

'
    Columns("A:A").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add(Range("A1:A116"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(192, 0 _
        , 0)
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:A116")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[CephasOz]

Give this a try,

Sub Sort_by_colour()
'
' Sort_by_colour Macro
'

'
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add(Range("A1:A116"), _
            xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(192, 0 _
            , 0)
        .SetRange Range("A1:A116")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[Xirom431]

Give this a try,

Sub Sort_by_colour()
'
' Sort_by_colour Macro
'

'
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add(Range("A1:A116"), _
            xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(192, 0 _
            , 0)
        .SetRange Range("A1:A116")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

How do I run on selected Sheets such as Sheet1, Sheet3, Sheet5, etc...

 

[CephasOz]

You could run it on a given sheet by first going to that sheet, which would make it the ActiveSheet, then run the macro.

Alternatively, if you always want it to be run on a given set of sheets, you could change it to:

Sub Sort_by_colour()
'
' Sort_by_colour Macro
'

'
    Dim varList As Variant
    Dim varWks As Variant
    Dim wksToUse As Worksheet
    '
    varList = Array("Sheet1", "Sheet3", "Sheet5")
    For Each varWks In varList
        Set wksToUse = ThisWorkbook.Worksheets(CStr(varWks))
        With wksToUse.Sort
            .SortFields.Clear
            .SortFields.Add(wksToUse.Range("A1:A116"), _
                xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(192, 0 _
                , 0)
            .SetRange wksToUse.Range("A1:A116")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    Next varWks
End Sub

Next question: Is the range always going to be A1:A116, or do you need that generalised as well?