VBA looping with defined name as criteria - rows

[cmholmes]

Hello,

I'm writing some protocol that prints out reports based on a list that I put into a defined name. So there are 14 locations that I want to run the report for that I used the defined name "Locations". I have a data dump worksheet that contains a list of users and there information. I want a filter list to be populated based on the location and two report pages to be saved to a new workbook.

So I figured our saving to a new workbook once, but looping it for new criteria each time and updating the report each time is were I got stumped. I'm currently using the "ROW" function to populate the filtered lists.

Defined Names:
Vaild: 1 if a current user, 0 for no
Unique_count: count of valid users for specific location
Locations: List of 14 locations by City

Let me know if you can point me in the right direction. I can add more of the code I've used so far if it will help clarify.

'loop formulas
.Range("A2").FormulaArray = "=IF(ROWS(A$2:A2)<=" & unique_count & ",INDEX(INDIRECT(A$1),SMALL(IF(Valid=1,ROW(Valid)-ROW(Data!$AP$2)+1),ROWS(A$2:A2))),"""")"
.Range("B2").FormulaArray = "=IF(ROWS(B$2:B2)<=" & unique_count & ",INDEX(INDIRECT(B$1),SMALL(IF(Valid=1,ROW(Valid)-ROW(Data!$AP$2)+1),ROWS(B$2:B2))),"""")"
.Range("C2").FormulaArray = "=IF(ROWS(C$2:C2)<=" & unique_count & ",INDEX(INDIRECT(C$1),SMALL(IF(Valid=1,ROW(Valid)-ROW(Data!$AP$2)+1),ROWS(C$2:C2))),"""")"
.Range("D2").FormulaArray = "=IF(ROWS(D$2:D2)<=" & unique_count & ",INDEX(INDIRECT(D$1),SMALL(IF(Valid=1,ROW(Valid)-ROW(Data!$AP$2)+1),ROWS(D$2:D2))),"""")"
.Range("E2").FormulaArray = "=IF(ROWS(E$2:E2)<=" & unique_count & ",INDEX(INDIRECT(E$1),SMALL(IF(Valid=1,ROW(Valid)-ROW(Data!$AP$2)+1),ROWS(E$2:E2))),"""")"
.Range("F2").FormulaArray = "=IF(ROWS(F$2:F2)<=" & unique_count & ",INDEX(INDIRECT(F$1),SMALL(IF(Valid=1,ROW(Valid)-ROW(Data!$AP$2)+1),ROWS(F$2:F2))),"""")"
.Range("G2").FormulaArray = "=IF(ROWS(G$2:G2)<=" & unique_count & ",INDEX(INDIRECT(G$1),SMALL(IF(Valid=1,ROW(Valid)-ROW(Data!$AP$2)+1),ROWS(G$2:G2))),"""")"



Sub Create_User_List_Workbook()

Dim ExternalLinks As Variant
Dim wb As Workbook
Dim x As Long




Sheets(Array("Users Report", "List")).Select
Sheets("List").Activate
Sheets(Array("Users Report", "List")).Copy


Set wb = ActiveWorkbook

'Unprotect Password
ActiveWorkbook.Worksheets("Users Report").Unprotect Password:="xxx"

Call DeleteDefinedNames
Call RemoveDataValidations_ActiveSheet


'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

'Password Protect
ActiveWorkbook.Worksheets("Users Report").Protect Password:="xxx"


'SaveAs
ActiveWorkbook.SaveAs Filename:= _
"J:\New - " & Worksheets("Users Report").Range("$B$5").Value _
& " - " & Format(Now(), "yyyy-mm-dd") & ".xlsx", FileFormat _
:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close


End Sub

 

[cmholmes]

I figured it out. I ended up not using the defined names. I just created a unique list on one of my reference sheets, referenced the first cell of my list and as it looped the first cell was deleted moving the next location up so that the other tabs would be calculated based on the new location.