Run macro that sorts and outputs data for several sheets from master worksheet

Question

Below is the list of names, and the macro code to sort and output the data. The macro runs separately for each name. I would like it to reuse the main body of the code, with the macro choosing each name, in turn, from the list

List of names:

Manny Joe Tom Mike

Sort and output code for Manny:

Sheets("Manny").Select
Range("A2:J1000").Select
Selection.ClearContents
Sheets("Master list").Select
Range("A1:J1000").Select
ActiveSheet.Range("$A1:J351").AutoFilter Field:=10, Criteria1:= _
        "Manny"
Selection.Copy
Sheets("Manny").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Columns("H:H").Select
ActiveWorkbook.Worksheets("Manny").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Manny").Sort.SortFields.Add Key:=Range("H1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Manny").Sort
    .SetRange Range("A2:J351")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
Columns("A:A").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Sheets("Master list").Select
ActiveSheet.Range("$A1:J350").AutoFilter Field:=10

You mean you want to make it dynamic, and not "hardcode" the name into the macro? How do you store your list of names? A range of cells? Is there any other change that has ro be made apart from the name, like the ranges? And also the obligatory link to [How to avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Christofer Weber, May 24 '21 at 13:59
The names can be stored on any convenient way. I can use a worksheet cell range, define a list, or any other suitable construct. Whatever works best. Only the names are chnged, the rest of the code is the same for every name. — aremjay, May 26 '21 at 20:41

score 0 · Answer 1 · answered May 24 '21 at 14:54

Consider generalizing your process in a Sub or Function to receive the name as parameter. However, as commented see top answers of canonical question, How to avoid using Select in Excel VBA, where you can avoid any and all .Select, .Activate, .Selection, ActiveSheet, .ActiveWorkbook, and unqualified references to workbook, workhseets, columns, and cell ranges.

Below is refactored code to process sheets by input parameter name, using named range, direct assignment of range values, and With context block. Adjustments may be needed. Be sure to incorporate other best practices: On Error, Option Explicit, Set object = Nothing, etc.

Public Sub sort_data(name As String)
   Dim master_rng As Range

   Set master_rng = ThisWorkbook.Sheets("Master list").Range("$A1:J351")
   master_rng.AutoFilter Field:=10, Criteria1:= name   ' USE OF INPUT PARAMETER

   With ThisWorkbook.Worksheets(name)                  ' USE OF INPUT PARAMETER
       .Range("A2:J1000").ClearContents
       .Range("A1:J351").Value = master_rng.value      ' REPLACEMENT OF COPY/PASTE
       .Columns("H:H").Sort.SortFields.Clear
       .Columns("H:H").Sort.SortFields.Add Key:=.Range("H1"), _
           SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

       With .Sort
          .SetRange ThisWorkbook.Worksheets(name).Range("A2:J351")
          .Header = xlNo
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
          .Apply
       End With

       .Columns("A:A").EntireColumn.AutoFit
       .Columns("E:E").EntireColumn.AutoFit
   End With

   master_rng.AutoFilter Field:=10

   Set master_rng = Nothing
End Sub

Then call above method individually or in loop for each needed name:

Call sort_data("Manny")
Call sort_data("Joe")
Call sort_data("Tom")
Call sort_data("Mike")

Dim nm As Variant

For Each nm in Array("Manny", "Joe", "Tom", "Mike")
    Call sort_data(nm)
Next nm

Run macro that sorts and outputs data for several sheets from master worksheet

1 Answers1