Share via

One Macro for multiple buttons

Anonymous
2017-02-06T18:34:33+00:00

My VBA script uses a button; when clicked it sorts data in a different sheet. The button is looks for a specific item, in this case "Parkersburg-Vienna WV". I refer to these items as locations. I have 20 of them. Is there a way to write one Macro and have the location as a variable. I want to avoid copying and pasting the same macro 20 times and, if I make changes, copy and paste 20 times again.

Here is the macro:

Private Sub CommandButton1_Click()

Dim End_of_Column_G As Range

'Clears all filters from Performance Report

On Error Resume Next

Sheets("Performance Report").ShowAllData

'Looks for last row in column G

With Sheets("Performance Report")

Set End_of_Column_G = .Range("G65536").End(xlUp)

End With

'Looks at Performance Report

Sheets("Performance Report").Select

'Sorts data - based on location

ThisWorkbook.Sheets("Performance Report").Range("A2" & ":" & End_of_Column_G.Address).AutoFilter Field:=1, Criteria1:="Parkersburg-Vienna WV"

'Sorts data - entire block, up to "End With" is to sort from smallest to largest Q-Score

ActiveWorkbook.Worksheets("Performance Report").AutoFilter.Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Performance Report").AutoFilter.Sort.SortFields.Add Key:=Range("D2" & ":" & End_of_Column_G.Offset(0, -3).Address), _

    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Performance Report").AutoFilter.Sort

    .Header = xlYes

    .MatchCase = False

    .Orientation = xlTopToBottom

    .SortMethod = xlPinYin

    .Apply

End With

'In the event that sheet is scrolled down

ActiveSheet.Range("A3").Select

End Sub

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

Answer accepted by question author

HansV 462.6K Reputation points
2017-02-06T20:22:43+00:00

Try this as starting point:

Private Sub CommandButton1_Click()

    Call ProcessLocation("Parkersburg-Vienna WV")

End Sub

Private Sub CommandButton2_Click()

    Call ProcessLocation("Hagerstown MD")

End Sub

' Etc.

Private Sub ProcessLocation(Location As String)

    Dim End_of_Column_G As Range

    On Error Resume Next

    With Sheets("Performance Report")

        .ShowAllData

        Set End_of_Column_G = .Range("G65536").End(xlUp)

        .Range("A2" & ":" & End_of_Column_G.Address).AutoFilter Field:=1, Criteria1:=Location

        With .AutoFilter.Sort

            .SortFields.Clear

            .SortFields.Add Key:=Range("D2" & ":" & End_of_Column_G.Offset(0, -3).Address), _

                SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

            .Header = xlYes

            .MatchCase = False

            .Orientation = xlTopToBottom

            .SortMethod = xlPinYin

            .Apply

        End With

    End With

End Sub

Was this answer helpful?

2 people found this answer helpful.
0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Anonymous
    2017-02-06T21:34:31+00:00

    This works incredibly well. Thank you so much!

    Was this answer helpful?

    0 comments No comments