Two questions... One, does it matter if one manager can see another managers
data? Two, do you have a column set up show the manager for each row of
data? If the answer is yes to both of those questions, then select the
entire column with the manager's names in it and then click
Data/Filter/AutoFilter on the menu bar. This will create a drop down in your
header for that column... click the down arrow and select a manager's name
from the list... all the other rows not belonging to that manager will be
hidden. Can you make use of this idea?

--
Rick (MVP - Excel)


"mcescher" wrote in message
...
On May 4, 10:40 am, "Jellifish" wrote:
Why not create queries in Access then you can use those as a base for your
report?

"mcescher" wrote in message

...



I have about 40k rows of info in a sheet. This goes to VP level
people. Now, they want the location specific information (25
locations) to go to the managers of those locations. So, I have to
make 25 copies of the book, and delete all the rows that don't go to
that location in each book.

So, I cycle through the rows, checking the manager and clearing the
row if it doesn't belong. Then I sort the remaining records to the
top. This works fine, I'm just wondering if there is a more efficient
(speedier) way to do this. FWIW, I'm doing this from MS Access 2003

strPathDest = strBIExt & "\InforceList" & strFileDate & "_" & !
SalesMgrFileName & ".xls"
FileCopy strPathSrc, strPathDest
Set xlBook = xlApp.Workbooks.Open(strPathDest)
'Grab Inforce and clean it out
Set xlSheet = xlBook.Worksheets("Inforce")
xlSheet.Activate
intRow = 2
Do While xlSheet.Range(R1C1toA1(intRow, 18)).Value ""
If xlSheet.Range(R1C1toA1(intRow, 18)).Value < !
SalesMgrXLName Then
Rows(intRow).Select
Selection.ClearContents
End If
intRow = intRow + 1
Loop
Rows("2:65536").Select
Selection.Sort Key1:=Range("C1"), Order1:=xlAscending,
Header:=xlYes
Range("A2").Select- Hide quoted text -

- Show quoted text -

Well, this is a small part of a bigger project, and we have five
databases building sheets in this workbook, and then the last one
makes the copies and cleans up each of the sheets.

It's not a super big deal, because it is automated, and runs fine, I
just wondered if this was the most efficient way to attack the
problem. My programming background is in Access, and I'm becoming
more confortable with Excel, but still have a ton to learn.

Thanks,
Chris M.