I have several lines of VBA code that help filter my data by certain criteria - any ideas to shorten my code?
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim ws As Worksheet
Dim sTableName As String
sTableName = "T_Data"
Dim loTable As ListObject
Set ws = Sheets("Data")
Set loTable = ws.ListObjects(sTableName)
If Target.Range.Address = "$D$3" Then
On Error Resume Next
ws.ShowAllData
loTable.Range.AutoFilter Field:=62, Criteria1:="<>Custom Staffing"
loTable.Range.AutoFilter Field:=55, Criteria1:="PASTSOC"
ws.Activate
Else
If Target.Range.Address = "$D$4" Then
On Error Resume Next
ws.ShowAllData
loTable.Range.AutoFilter Field:=61, Criteria1:="MI THH Staffing"
loTable.Range.AutoFilter Field:=55, Criteria1:="PASTSOC"
ws.Activate
Else
If Target.Range.Address = "$D$5" Then
On Error Resume Next
ws.ShowAllData
loTable.Range.AutoFilter Field:=52, Criteria1:="DME"
loTable.Range.AutoFilter Field:=55, Criteria1:="PASTSOC"
loTable.Range.AutoFilter Field:=62, Criteria1:="<>Custom Staffing"
ws.Activate
Else
If Target.Range.Address = "$D$6" Then
On Error Resume Next
ws.ShowAllData
loTable.Range.AutoFilter Field:=49, Criteria1:="Enteral"
loTable.Range.AutoFilter Field:=55, Criteria1:="PASTSOC"
loTable.Range.AutoFilter Field:=62, Criteria1:="<>Custom Staffing"
ws.Activate
Else
If Target.Range.Address = "$D$7" Then........
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim ws As Worksheet
Dim sTableName As String
sTableName = "T_Data"
Dim loTable As ListObject
Set ws = Sheets("Data")
Set loTable = ws.ListObjects(sTableName)
If Target.Range.Address = "$D$3" Then
On Error Resume Next
ws.ShowAllData
loTable.Range.AutoFilter Field:=62, Criteria1:="<>Custom Staffing"
loTable.Range.AutoFilter Field:=55, Criteria1:="PASTSOC"
ws.Activate
Else
If Target.Range.Address = "$D$4" Then
On Error Resume Next
ws.ShowAllData
loTable.Range.AutoFilter Field:=61, Criteria1:="MI THH Staffing"
loTable.Range.AutoFilter Field:=55, Criteria1:="PASTSOC"
ws.Activate
Else
If Target.Range.Address = "$D$5" Then
On Error Resume Next
ws.ShowAllData
loTable.Range.AutoFilter Field:=52, Criteria1:="DME"
loTable.Range.AutoFilter Field:=55, Criteria1:="PASTSOC"
loTable.Range.AutoFilter Field:=62, Criteria1:="<>Custom Staffing"
ws.Activate
Else
If Target.Range.Address = "$D$6" Then
On Error Resume Next
ws.ShowAllData
loTable.Range.AutoFilter Field:=49, Criteria1:="Enteral"
loTable.Range.AutoFilter Field:=55, Criteria1:="PASTSOC"
loTable.Range.AutoFilter Field:=62, Criteria1:="<>Custom Staffing"
ws.Activate
Else
If Target.Range.Address = "$D$7" Then........