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:
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!!
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!!