create a form ,on it is a listbox of all business units.
the code will cycle thru the list, and export the query based on the bus.unit
the query uses the listbox, lstUnit , to pull data, ie:
select * from table where [busUnit]=forms!myForm!lstUnit
Code:
sub btnMake_click()
dim i as integer
dim sUnit as string
dim vFile
for i = 0 to lstUnit.listcount - 1
sUnit = lstUnit.ItemData(i) 'get next item in list
lstUnit = sUnit 'set list to that item for the query to read
vFile = "c:\temp\" & sUnit & ".xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qsExport1UnitPayments", vFile, True,"Payments"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qsExport1UnitChrgs", vFile, True,"Charges"
next
end sub