Loops of file, filter, save

[AnnieLox]

hello all! i'm trying to learn to code and have worked through some old broken code for years trying to help my department function. i'm now trying to rebuild the code and am in need of some assistance in building this code.

i have a list of people, with a datasheet needed for each. i have the code written long, but i need it now to repeat the code for each name on the list, because our list is becoming more dynamic.


I clipped the code to show two names. but i need to expand it to include additional people from other departments with a separate filtered view.

currently the code is as such:

Sub SaveAs()


    ActiveWorkbook.SaveAs "H:\STP Report" & "\" & Format(Now, "dd.mm.yyyy") & " HOU.xlsm"


Windows(Format(Now, "dd.mm.yyyy") & " HOU.xlsm").Activate
    


Dim wb As Workbook
Set wb = Workbooks.Add
ThisWorkbook.Sheets("Data").Copy Before:=wb.Sheets(1)
ActiveWorkbook.Worksheets("Data").AutoFilter.Sort.SortFields.Clear
'change made here 01.11.19
'removed this line''  ActiveSheet.Range("$A$1:$AG$12000").AutoFilter Field:=4, Criteria1:="Annie"
    Sheets("Data").Select
    Sheets("Data").Copy After:=Sheets("Data")
    Sheets("Data (2)").Select
    Sheets("Data (2)").Name = "backup"
        Cells.Select
    Range("D1").Activate
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    
    
    Sheets("Data").Select
    Dim cell As Range


    ActiveSheet.UsedRange.AutoFilter Field:=4, Criteria1:="<>Annie"
    
    On Error Resume Next
        ActiveSheet.UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
    On Error GoTo 0
    
    ActiveSheet.ShowAllData
Dim ExternalLinks As Variant
Dim x As Long


Set wb = ActiveWorkbook


'Create an Array of all External Links stored in Workbook
  ExternalLinks = wb.LinkSources(Type:=xlLinkTypeExcelLinks)


'Loop Through each External Link in ActiveWorkbook and Break it
  For x = 1 To UBound(ExternalLinks)
    wb.BreakLink Name:=ExternalLinks(x), Type:=xlLinkTypeExcelLinks
  Next x
wb.SaveAs "H:\Data Sheets\" & Format(Now, "dd.mm.yyyy") & " Annie.xlsx"
ActiveWorkbook.Close




Windows(Format(Now, "dd.mm.yyyy") & " HOU.xlsm").Activate


Set wb = Workbooks.Add
ThisWorkbook.Sheets("Data").Copy Before:=wb.Sheets(1)
ActiveWorkbook.Worksheets("Data").AutoFilter.Sort.SortFields.Clear
'change made here 01.11.19
'removed this line''  ActiveSheet.Range("$A$1:$AG$12000").AutoFilter Field:=4, Criteria1:="Cecile"
    Sheets("Data").Select
    Sheets("Data").Copy After:=Sheets("Data")
    Sheets("Data (2)").Select
    Sheets("Data (2)").Name = "backup"
        Cells.Select
    Range("D1").Activate
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    
    
    Sheets("Data").Select




    ActiveSheet.UsedRange.AutoFilter Field:=4, Criteria1:="<>Cecile"
    
    On Error Resume Next
        ActiveSheet.UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
    On Error GoTo 0
    
    ActiveSheet.ShowAllData


Set wb = ActiveWorkbook


'Create an Array of all External Links stored in Workbook
  ExternalLinks = wb.LinkSources(Type:=xlLinkTypeExcelLinks)


'Loop Through each External Link in ActiveWorkbook and Break it
  For x = 1 To UBound(ExternalLinks)
    wb.BreakLink Name:=ExternalLinks(x), Type:=xlLinkTypeExcelLinks
  Next x
wb.SaveAs "H:\Data Sheets\" & Format(Now, "dd.mm.yyyy") & " Cecile.xlsx"
ActiveWorkbook.Close

I'd like to change the code so that it can run from a loop
the count column is for me to count how many individuals with that name are in the department and if it is more than 1, saving the file as Annie1075 instead of Annie. the Datasheet will either show backend or front end depenting on if our operational team view will be utilized for that datasheet.
i have my table set up:

name	daily generate	Datasheet	Display as	count	Display ext	ext	department	file location
Annie Od	1	Backend	Annie	1	Annie1075	1075	cost control	H:\STP REPORT\TEMPLATE\SANDBOX\VENDOR INVOICE REPORTS\DATASHEETS\CC DATA

<tbody>
</tbody>

thank you to any and all that can help!!