I have an Excel 2010 spreadsheet like the one shown in Fig 1 below. At
work I am continually asked for "group" reports which means that the
requestor wants to see everyone in so and so's group. For example, if
I get a request to see everyone in David Drew's group then the
finished report would look like Fig 2. below. A dream macro would be
one that prompts me for a Last name, lets me input a name, then
presto, places a report like Fig 2 on sheet2 of the workbook. Can
anyone help me with the code that might do something like this? Thanks
for entertaining my plight.

Fig 1.

FirstName Lastname EmpID MgrEmpID Email Location
Alex Anderson aaaa noManager Philadelphia
Brooke Brown bbbb aaaa Philadelphia
Carol Clark cccc aaaa Philadelphia
David Drew dddd aaaa Philadelphia
Erin Eisley eeee bbbb Phoenix
Felicia Ford ffff bbbb Phoenix
Grace Griffin gggg cccc Phoenix
Henry Howard hhhh cccc Phoenix
Ian Iverson iiii dddd New Orleans
Janice Jones jjjj dddd New Orleans
Karl King kkkk eeee New Orleans
Linda Lewis llll eeee New Orleans
Mike Miller mmmm ffff Boston
Nancy Nash nnnn ffff Boston
Olivia Ollie oooo gggg Boston
Paula Price pppp gggg Boston
Quentin Quincy qqqq hhhh Cleveland
Robert Richardson rrrr hhhh Cleveland
Steve Simmons ssss iiii Cleveland
Tina Thomas tttt iiii Cleveland
Ursula Urban uuuu jjjj Houston
Veronica Valooshka vvvv jjjj Houston
Walter Watson wwww aaaa Houston
Xenon Xandruski xxxx wwww Houston
Yolanda Young yyyy xxxx Dallas
Zachary Zubov zzzz xxxx Dallas



Fig 2

FirstName Lastname EmpID MgrEmpID Email Location
Ian Iverson iiii dddd New Orleans
Steve Simmons ssss iiii Cleveland
Tina Thomas tttt iiii Cleveland
Janice Jones jjjj dddd New Orleans
Ursula Urban uuuu jjjj Houston
Veronica Valooshka vvvv jjjj Houston

How do we know which group each member is in?? Then, it's a simple
matter to use datafilterautofilterfilter by group number or letter.
Send me you file if you like. dguillett1 @gmail.com

On Jan 4, 2:41*pm, John Menken wrote:
I have an Excel 2010 spreadsheet like the one shown in Fig 1 below. At
work I am continually asked for "group" reports which means that the
requestor wants to see everyone in so and so's group. For example, if
I get a request to see everyone in David Drew's group then the
finished report would look like Fig 2. below. A dream macro would be
one that prompts me for a Last name, lets me input a name, then
presto, places a report like Fig 2 on sheet2 of the workbook. Can
anyone help me with the code that might do something like this? Thanks
for entertaining my plight.

Fig 1.

FirstName * * * Lastname * * * *EmpID * MgrEmpID * * * *Email * Location
Alex * *Anderson * * * *aaaa * *noManager * * * Philadelphia
Brooke *Brown * bbbb * *aaaa * *Philadelphia
Carol * Clark * cccc * *aaaa * * Philadelphia
David * Drew * *dddd * *aaaa * * *Philadelphia
Erin * *Eisley *eeee * *bbbb * * Phoenix
Felicia Ford * *ffff * *bbbb * *Phoenix
Grace * Griffin gggg * *cccc * Phoenix
Henry * Howard *hhhh * *cccc * *Phoenix
Ian * * Iverson iiii * *dddd * * New Orleans
Janice *Jones * jjjj * *dddd * *New Orleans
Karl * *King * *kkkk * *eeee * * * New Orleans
Linda * Lewis * llll * *eeee * * New Orleans
Mike * *Miller *mmmm * *ffff * * Boston
Nancy * Nash * *nnnn * *ffff * * *Boston
Olivia *Ollie * oooo * *gggg * *Boston
Paula * Price * pppp * *gggg * * Boston
Quentin Quincy *qqqq * *hhhh * * * * *Cleveland
Robert *Richardson * * *rrrr * *hhhh * * * Cleveland
Steve * Simmons ssss * *iiii * Cleveland
Tina * *Thomas *tttt * *iiii * * Cleveland
Ursula *Urban * uuuu * *jjjj * *Houston
Veronica * * * *Valooshka * * * vvvv * *jjjj * * *Houston
Walter *Watson *wwww * *aaaa * Houston
Xenon * Xandruski * * * xxxx * *wwww * * * * Houston
Yolanda Young * yyyy * *xxxx * Dallas
Zachary Zubov * zzzz * *xxxx * Dallas

Fig 2

FirstName * * * Lastname * * * *EmpID * MgrEmpID * * * *Email * Location
Ian * * Iverson iiii * *dddd * * New Orleans
Steve * Simmons ssss * *iiii * Cleveland
Tina * *Thomas *tttt * *iiii * * Cleveland
Janice *Jones * jjjj * *dddd * *New Orleans
Ursula *Urban * uuuu * *jjjj * *Houston
Veronica * * * *Valooshka * * * vvvv * *jjjj * * *Houston

We don't know what group any one employee is in.
What we know is that person's managerEmployeeID number.
Therefore through a recursive query we should be able to arrive at the
answer.
I used to do this with loops in a web programming language but I do
not know how to go about it in Visual Basic for Excel.
Thank you.

On Wed, 4 Jan 2012 12:41:39 -0800 (PST), John Menken wrote:

I have an Excel 2010 spreadsheet like the one shown in Fig 1 below. At
work I am continually asked for "group" reports which means that the
requestor wants to see everyone in so and so's group. For example, if
I get a request to see everyone in David Drew's group then the
finished report would look like Fig 2. below. A dream macro would be
one that prompts me for a Last name, lets me input a name, then
presto, places a report like Fig 2 on sheet2 of the workbook. Can
anyone help me with the code that might do something like this? Thanks
for entertaining my plight.

This needs considerable "prettying up" but should get you started. It's getting late so I did no more than the basics.
Given that you want to be prompted for a name, I decided to construct a list of the last names only, and have that pop up in a List Box (User Form)

I am assuming you have some familiarity with the VBE.

Some of the assumptions:
Your main data is on Sheet1
Your report will be on Sheet2
There is nothing else of value on Sheet2
There are no duplicate last names (with different first names) to be considered.
Column F on your main data sheet has an entry for every (or at least for the last) row.

Some areas to "pretty up"
Formatting
Have OK Cancel buttons on the User Form and the associated code to allow a more elegant exit other than just closing the form with the upper right "X" button.
Have some code to make sure that autofilter is NOT enabled on Sheet1 when you run the macro, else the macro will crash.

Here is your main code which should go into a "regular" module. That is produced by ensuring your project is highlighted in the VBE; then selecting Insert/Module from the main menu bar:

========================================
Public rTable1 As Range
Public sManager As String
Option Explicit
Sub GroupReport()
Dim rReport As Range
Dim colCriteria As Collection
Dim Criteria As String, aCriteria() As String
Dim c As Range
Dim i As Long
Dim sFirstAddress As String

'assumes column F filled all the way down
With Worksheets("Sheet1")
Set rTable1 = .Range("A1", .Cells(.Rows.Count, "F").End(xlUp))
End With
Set rReport = Worksheets("Sheet2").Cells(1, 1)
Set colCriteria = New Collection

UserForm1.Show
'Selected Manager now in sManager

Criteria = rTable1.Columns(2).Find(what:=sManager, LookIn:=xlValues, _
lookat:=xlWhole).Offset(columnoffset:=1).Text

colCriteria.Add Item:=Criteria, Key:=Criteria

With rTable1.Columns(4)
Do
For i = 1 To colCriteria.Count
Set c = .Find(what:=colCriteria(i))
If Not c Is Nothing Then
sFirstAddress = c.Address
Do
'use Collection to prevent duplicates
On Error Resume Next
colCriteria.Add Item:=c.Offset(columnoffset:=-1).Text, _
Key:=c.Offset(columnoffset:=-1)
On Error GoTo 0
Set c = .FindNext(c)
Loop While Not c Is Nothing And sFirstAddress < c.Address
End If
Next i
Loop Until i colCriteria.Count
End With

ReDim aCriteria(1 To colCriteria.Count)
For i = 1 To colCriteria.Count
aCriteria(i) = colCriteria(i)
Next i

rTable1.AutoFilter field:=4, Criteria1:=aCriteria, _
Operator:=xlFilterValues

rReport.Worksheet.Cells.Clear
rTable1.SpecialCells(xlCellTypeVisible).Copy Destination:=rReport
rReport.Worksheet.Cells.EntireColumn.AutoFit

rTable1.AutoFilter
End Sub
=========================================

For the User Form, select Insert/User Form. A design window will open on which you will draw a User Form; and then draw a ListBox inside the user form.
The UserForm has a name of "UserForm1"
The ListBox is named "ManagerNames"

The code behind the UserForm (which you get to by right-clicking on the userform design and selecting View Code) is as below:

============================
Option Explicit
Private Sub ManagerNames_Click()
sManager = ManagerNames.Text
End Sub

Private Sub userform_initialize()
Dim ManagerList()
Dim i As Long
ManagerList = WorksheetFunction.Transpose(rTable1.Resize( _
rowsize:=rTable1.Rows.Count - 1, columnsize:=1). _
Offset(rowoffset:=1, columnoffset:=1))

For i = LBound(ManagerList) To UBound(ManagerList)
ManagerNames.AddItem ManagerList(i)
Next i

End Sub
==============================

If you've done all this correctly, when you execute the macro, the form should pop up with a box of last names of which you can select only one. Select that name and exit the box by clicking the "X" in the upper right corner. The macro then determines the filtering to use for the autofilter in order to produce your desired report. It will then clear Sheet2, write the report to sheet2, and adjust the column widths.

Finally it will turn off the autofilter for the data. This is important since if the macro starts with the autofilter enabled, it will crash.

Wow, unreal. Thank you so much. I can't wait to try this.
I really appreciate the help.

Just tried it.
Worked flawlessly.
I will make the improvements that you suggested.
Thank you again.

On Thu, 5 Jan 2012 07:12:00 -0800 (PST), John Menken wrote:

Wow, unreal. Thank you so much. I can't wait to try this.
I really appreciate the help.

Let me know how it works out.

By the way, to ensure that AutoFilter is turned off before you start running the macro, insert this at the beginning of the "regular" macro:

Replace:
=======================
'assumes column F filled all the way down
With Worksheets("Sheet1")
Set rTable1 = .Range("A1", .Cells(.Rows.Count, "F").End(xlUp))
End With
=======================


===============================
'assumes column F filled all the way down
With Worksheets("Sheet1")
.AutoFilterMode = False
Set rTable1 = .Range("A1", .Cells(.Rows.Count, "F").End(xlUp))
End With
================================

You are just adding the

.AutoFilterMode = False

statement near the beginning.

On Thu, 5 Jan 2012 08:10:25 -0800 (PST), John Menken wrote:

Just tried it.
Worked flawlessly.
I will make the improvements that you suggested.
Thank you again.

Glad to help. Thanks for the feedback.

By the way, your clear and concise description of what you wanted, along with the examples of the input and expected output, was the most important factor in allowing a solution to be crafted that suited your needs.

Hello - I have a similar need in that I have a report structured in the same manner as John's below, my only difference is that instead of running this vba code for one name at a time, I would like to run for everyone on the report at once, ideally having the data broken out as a separate worksheet tab per person... the ultimate goal is to get the number of direct and indirect reports of any given person.

Example of desired results:
Worksheet tab 1: John Smith's direct and indirect reports
FirstName Lastname EmpID MgrEmpID Email Location
John Smith xxxxx xxxxx Timbucktoo

Worksheet tab 2: Jane Smith's direct and indirect reports
FirstName Lastname EmpID MgrEmpID Email Location
John Smith xxxxx xxxxx Timbucktoo

And so on...

Any thoughts or code modifications to the already extremely helpful VBA code above would be greatly appreciated!

Regards