CODEWITHSUNDEEP
vbaexcelworksheet
Vetle Andre Remme Olsen
Vetle Andre Remme Olsen

Reputation: 71

Run one macro in multiple sheets

How can I make my macros run not just in this sheet, but other sheets as well?

Meaning that when I have two or more identical sheets and I want the same macro to work on both of them independently triggered by a button.

Sub sort_test()
'
' sort_test Macro
'
'
    Range("M4:O7").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add(Range("O5:O7"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 0 _
        , 0)
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add(Range("O5:O7"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
        192, 0)
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add(Range("O5:O7"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(192, 0 _
        , 0)
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("M4:O7")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("M4").Select
End Sub

Upvotes: 0

Views: 863

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

Untested, but something like this:

Sub Test1()
    DoSort ThisWorkbook.Sheets("Sheet1")
End Sub

Sub Test2()
    DoSort ThisWorkbook.Sheets("Sheet2")
End Sub

Sub DoSort(sht As Worksheet)
    With sht.Sort

        .SortFields.Clear
        .SortFields.Add(sht.Range("O5:O7"), xlSortOnCellColor, xlAscending, _
            , xlSortNormal).SortOnValue.Color = RGB(255, 0, 0)
        .SortFields.Add(sht.Range("O5:O7"), xlSortOnCellColor, xlAscending, _
            , xlSortNormal).SortOnValue.Color = RGB(255, 192, 0)
        .SortFields.Add(sht.Range("O5:O7"), xlSortOnCellColor, xlAscending, _
            , xlSortNormal).SortOnValue.Color = RGB(192, 0, 0)

        .SetRange sht.Range("M4:O7")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply

    End With

    sht.Range("M4").Select

End Sub

If you just want a button to sort the activesheet then just use ActiveSheet in your existing code

Upvotes: 2

Related Questions