Loops of file, filter, save

AnnieLox

New Member
Joined
Sep 18, 2017
Messages
22
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:
Rich (BB code):
Rich (BB code):
Rich (BB code):
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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]name[/TD]
[TD]daily generate[/TD]
[TD]Datasheet[/TD]
[TD]Display as[/TD]
[TD]count[/TD]
[TD]Display ext[/TD]
[TD]ext[/TD]
[TD]department[/TD]
[TD]file location[/TD]
[/TR]
[TR]
[TD]Annie Od[/TD]
[TD]1[/TD]
[TD]Backend[/TD]
[TD]Annie[/TD]
[TD]1[/TD]
[TD]Annie1075[/TD]
[TD]1075[/TD]
[TD]cost control[/TD]
[TD]H:\STP REPORT\TEMPLATE\SANDBOX\VENDOR INVOICE REPORTS\DATASHEETS\CC DATA[/TD]
[/TR]
</tbody>[/TABLE]




thank you to any and all that can help!!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top