Public Sub SendItAll() ' Clear out any old data on Report Sheets("REPORT").Select Range("A1").CurrentRegion.ClearContents ' Sort data by name Sheets("PHREIMB").Select Range("A1").CurrentRegion.Select Selection.Sort Key1:=Range("A1"), Header:=xlYes ' Process each record on Distribution Sheets("Distribution").Select FinalRow = Range("A15000").End(xlUp).Row For i = 2 To FinalRow Sheets("Distribution").Select RegionToGet = Range("A" & i).Value Subject = "REPORT" Recipient = Range("D" & i).Value ' Clear out any old data on Report Sheets("REPORT").Select Range("A1").CurrentRegion.ClearContents ' Get records from Data Sheets("PHREIMB").Select Range("A1").CurrentRegion.Select ' Turn on AutoFilter, if it is not on If ActiveSheet.AutoFilterMode = False Then Selection.AutoFilter ' Filter the data to just this region Selection.AutoFilter Field:=1, Criteria1:=RegionToGet ' Select only the visible cells and copy to Report Selection.SpecialCells(xlCellTypeVisible).Select Selection.Copy Destination:=Sheets("REPORT").Range("A1") ' Turn off the Autofilter Selection.AutoFilter ' Copy the Report sheet to a new book and e-mail Sheets("REPORT").Copy Application.Dialogs(xlDialogSendMail).Show _ arg1:=Recipient, _ arg2:="REPORT" & " " & RegionToGet ActiveWorkbook.Close SaveChanges:=False Next i End Sub
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)