sort by name when user selects name on another worksheet

[Mathew]

Is there any want to select a value on one worksheet, using a pick list, and
having that sort data by the name on another sheet? For example in a sheet
named “Start Here†Cell B3 has a pick list with names in it, like Joe, bill,
bob, henry etc. If the user selects Joe on that worksheet, then another
worksheet named “Employees Info 2-25-10†is sorted by that name, i.e. Joe?
Is this possible, if so how? Any help would be appreciated!

 

[JLatham]

I think that you mean data filtered rather than sorted? In other words, when
you select "Joe" on the Start Here sheet, then the Employees Info sheet will
only display entries with Joe in a particular column?

If so, here's how to go about getting this done. First, to keep from
constantly filtering the "Employees Info..." sheet each time someone picks a
new name in B3, I'd have the "Employees Info..." sheet react when it is
selected.

Start by recording a new macro while you go through the process of filtering
the data on the "Employees Info..." sheet by any name in the list. You will
use the code it generates as the basis for your automation action.

It may record something that looks like this:
Sub Macro1()
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Joe"
End Sub

Now go to the "Employee Info..." sheet, right-click on it's name tab and
choose [View Code]. Copy the code here and paste it into the module
presented to you:

Private Sub Worksheet_Activate()

End Sub

Now go get the code from inside of the Sub Macro1() routine and copy it and
paste it in between the 2 lines of code above, so that you have something
that looks like

Private Sub Worksheet_Activate()
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Joe"
End Sub

Now all you have to do is change the "Joe" to reference B3 on the Start here
sheet, so that the code ends up looking something like:
Private Sub Worksheet_Activate()
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:= _
ThisWorkbook.Worksheets("Start Here").Range("B3")
End Sub

 

[Mathew]

JLatham: Very insightful and dead on correct. I did mean sort, in my
question, and your help solved the problem! Thank you!

I think that you mean data filtered rather than sorted? In other words, when
you select "Joe" on the Start Here sheet, then the Employees Info sheet will
only display entries with Joe in a particular column?

If so, here's how to go about getting this done. First, to keep from
constantly filtering the "Employees Info..." sheet each time someone picks a
new name in B3, I'd have the "Employees Info..." sheet react when it is
selected.

Start by recording a new macro while you go through the process of filtering
the data on the "Employees Info..." sheet by any name in the list. You will
use the code it generates as the basis for your automation action.

It may record something that looks like this:
Sub Macro1()
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Joe"
End Sub

Now go to the "Employee Info..." sheet, right-click on it's name tab and
choose [View Code]. Copy the code here and paste it into the module
presented to you:

Private Sub Worksheet_Activate()

End Sub

Now go get the code from inside of the Sub Macro1() routine and copy it and
paste it in between the 2 lines of code above, so that you have something
that looks like

Private Sub Worksheet_Activate()
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Joe"
End Sub

Now all you have to do is change the "Joe" to reference B3 on the Start here
sheet, so that the code ends up looking something like:
Private Sub Worksheet_Activate()
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:= _
ThisWorkbook.Worksheets("Start Here").Range("B3")
End Sub

Is there any want to select a value on one worksheet, using a pick list, and
having that sort data by the name on another sheet? For example in a sheet
named “Start Here†Cell B3 has a pick list with names in it, like Joe, bill,
bob, henry etc. If the user selects Joe on that worksheet, then another
worksheet named “Employees Info 2-25-10†is sorted by that name, i.e. Joe?
Is this possible, if so how? Any help would be appreciated!