0

New to VBA and have built the below, however I am running the code 17 times as there are 17 different teams and thus 17 different filtering variations. I feel like this is too complicated and has made the file unstable. Any help to simplify this would really be appreciated

Sheets("All Data").Select
Sheets("All Data").Rows(8).Select
Selection.AutoFilter

Sheets("All Data").Cells(1).AutoFilter Field:=5, Criteria1:=Array("name1", "name2", "name3", 
"name4"), Operator:=xlFilterValues
Sheets("All Data").UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Select

Range("A8").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

Selection.Copy

Sheets("team1").Select
Range("A42").Select

ActiveSheet.Paste
    
Sheets("All Data").Activate
Sheets("All Data").Rows(8).Select
Selection.AutoFilter
Sheets("All Data").Cells(1, 1).Select
        
        Application.CutCopyMode = False
        
Sheets("team1").Select
Range("N42").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("team1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("team1").AutoFilter.Sort.SortFields.Add2 Key:=Range _
    ("N42:N10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("team1").AutoFilter.Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom`
    .SortMethod = xlPinYin
    .Apply
End With'''
braX
  • 11,506
  • 5
  • 20
  • 33
FrankMoses10
  • 1
  • Do you mean that you need to have the code 17 times, with different worksheets (instead of "team1" sheet)? Also, you can shorten the code substantially if you [avoid using .select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Christofer Weber Oct 13 '21 at 09:25
  • And if you change "team1" to a variable, you can easily change which team you want to use when you run the code, and thus you can use the same code for any team. Then you can loop it, or select team when running it, or however you want to control it. – Christofer Weber Oct 13 '21 at 09:31
  • What you need is to create a array variable with the list of 17 teams and their respective filer criteria. But without providing those details, its difficult to confirm if that's what you're looking for. – Gangula Oct 13 '21 at 10:56
  • Thank you all, what I need the following: filter by name1, name2, name3 (from the all data sheet) to team1 (team1 sheet), then name6 name7, name8 (from the all data sheet) to team2 (team 2 sheet). I have a list of 38 team members split across 17 teams (this is all listed in the all data sheet). I need to copy the list of names from a specific team in the all data sheet to a separate sheet for that team only. Hope that makes sense and thank you – FrankMoses10 Oct 13 '21 at 13:08
  • Christofer Weber how do I do that? New to the VBA world. Thanks – FrankMoses10 Oct 13 '21 at 15:00
  • Do you have (or can you add) a sheet with 2 columns where Column A woukd be a list of Team Member Names and in columns B the corresponding Team Name for that member ? – CDP1802 Oct 13 '21 at 15:14
  • @FrankMoses10, can you post images of our ``All Data`` and ``Team1`` sheets? – Elio Fernandes Oct 14 '21 at 14:39
  • I have the following columns - opportunity code, opportunity owner, opportunity name, expected revenue, expect project start date, expected contract end date. I am filtering the data by opportunity owner and then copying all of the data to the various team sheets – FrankMoses10 Oct 15 '21 at 09:07
  • Is opportunity owner in column E, and row 8 a header ? – CDP1802 Oct 15 '21 at 11:18

0 Answers0