macro advance filter loop from list

[L7B]

Hi
I have a Macro (Sub Filterscorcards) works perfectly for what I want it to do.

Only issue is I need it to repeat the process for every individual in the list on the sheet (Scorecard). The number in the list are in column B.

Thanks for the help

Sub Filterscorecards()
Dim Itm As Long
Dim ws As Worksheet, MyArr As Variant, SvPath As String
Dim Sourcewb As Workbook
Dim Destwb As Workbook
With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
 
    Set Sourcewb = ActiveWorkbook
 
'Sheet with data in it
    Set ws = Sheets("Master")
 
 
'Filters report by scorecard holder on column C
    ws.Range("$A$8:$BM$409").AutoFilter Field:=3, Criteria1:=Array("1" _
        , "a", "e", "f", "m", "p"), Operator:=xlFilterValues
 
 
 'Copies to new Workbook
    ws.Range("A1:BM420").Copy
    Workbooks.Add
    ActiveSheet.Paste
 
  Set Destwb = ActiveWorkbook
  With Destwb
 
MyArr = Range("L1").Value
 'Formats sheet
    ActiveWindow.DisplayGridlines = False
    Rows("1:7").RowHeight = 18
    Rows("8:8").RowHeight = 51
    Rows("9:420").RowHeight = 18
    Rows("9:420").WrapText = False
    Cells.EntireColumn.AutoFit
    Range("I:I,M:M").ColumnWidth = 2
    Range("AG6:AM6,AO6:AU6,AW6:BC6,BE6:BK6,AG7:AM7,AO7:AU7,AW7:BC7,BE7:BK7").Merge
    ActiveWindow.Zoom = 70
    Range("A:F").Group
    Range("P:AF").Group
    ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
    NewName = Range("L1").Value
    ActiveSheet.Name = NewName
    Range("G1").Select
 
   'Save the new workbook and close it
        FileExtStr = ".xlsm"
        FileFormatNum = 52
    SvPath = "C:\Users\Lee\Work\"
 
With Destwb
        .SaveAs SvPath & MyArr & FileExtStr, FileFormat:=FileFormatNum
        .Close
 
    End With
 
    End With
End Sub

Thanks
Lee

 

[T_Holliday]

Ok, just trying to get a better idea of what you're doing:

You have a list of names on a worksheet [Workbooks("Scoresheet.xlsm").Sheets("Master")] aka sourcewb.sheets("Master"). For every item on the list on that worksheet, you want to create a new workbook? Or just one workbook with sheets for each item?

 

[L7B]

Mater is a data sheet which filters with the criteria

'Filters report by scorecard holder on column C
    ws.Range("$A$8:$BM$409").AutoFilter Field:=3, Criteria1:=Array("1" _
        , "a", "e", "f", "m", "p"), Operator:=xlFilterValues

The Array"1" is currently picking up scorecard 1. This is where it needs to be able to pick up all 42 scorecards create the report and format then save to new workbook with name.

Sorry dont know how to upload report here fore you to see.

Lee