How to make a directory in Excel

[rymigo]

I'm trying to make a directory in Excel. Say I have a sheet that lists all of the members of an organization such as this:

Member # Name Date Joined
27 Smith, Bob 04/05/1998
42 Jones, Larry 06/07/2000
143 Wilson, Frank 08/09/2002
197 Stevens, Mary 10/11/2004

Is there a way to create a sheet that will automatically pull out all of the last names that start with the letter "S," for example? So that I would get another sheet for S last names like this:

Member # Name Date Joined
27 Smith, Bob 04/05/1998
197 Stevens, Mary 10/11/2004

Thank you for your help!

 

[ParamRay]

I am making the following assumptions:
1) Member # is in column A
2) Name is in column B
3) Date Joined is in column C
4) Headers are in row 1 with data starting in row 2

Here is some code. Please adjust the sheet name on the line indicated.

Public Sub FilterByLastName()
  Const strDATA_SHEET = "Sheet1"   '<--- Set name of sheet containing the data here
  Dim wksCriteria As Worksheet
  Dim wksOutput As Worksheet
  Dim wksData As Worksheet
  Dim i As Integer
  
  On Error GoTo ErrorHandler
  Application.DisplayAlerts = False
  Set wksData = ThisWorkbook.Sheets(strDATA_SHEET)
  Set wksCriteria = ThisWorkbook.Sheets.Add()
  wksCriteria.Range("A1").Value = wksData.Range("B1").Value
  
  For i = Asc("A") To Asc("Z")
    On Error Resume Next
    Set wksOutput = ThisWorkbook.Sheets("Last Name - " & Chr(i))
    
    On Error GoTo ErrorHandler
    If wksOutput Is Nothing Then
      With ThisWorkbook
        Set wksOutput = .Sheets.Add(After:=.Sheets(.Sheets.Count))
        wksOutput.Name = "Last Name - " & Chr(i)
      End With
    Else
      wksOutput.Cells.Clear
    End If
    
    wksCriteria.Range("A2").Value = Chr(i)
    wksData.Range("A1").CurrentRegion.AdvancedFilter _
      Action:=xlFilterCopy, _
      CriteriaRange:=wksCriteria.Range("A1:A2"), _
      CopyToRange:=wksOutput.Range("A1")
    Set wksOutput = Nothing
  Next i
  
ExitHandler:
  On Error Resume Next
  wksCriteria.Delete
  wksData.ShowAllData
  wksData.Activate
  Application.DisplayAlerts = True
  Set wksCriteria = Nothing
  Set wksOutput = Nothing
  Set wksData = Nothing
  Exit Sub

ErrorHandler:
  MsgBox Err.Description, vbExclamation
  Resume ExitHandler
End Sub