Aussiebear
05-16-2010, 04:40 AM
I had hoped that the following code will call a sub to bring into effect an advanced filter of a Employees Qualification matrix. However it fails because it claims the "Call Filter_Crews" line of code is not defined.
Cell B2 on the Crew Allocation sheet is a drop down list comprising the values A, B, C, D, All.
Private Sub Worksheet_Change(ByVal Target As Range)
With Worksheets("Crew Allocations")
If Intersect(Target, Range("B2")) Is Nothing Then
Exit Sub
Else
With Worksheets("Qualifications")
Call Filter_Crews
End With
End If
End With
End Sub
The following code resides on the worksheet "Qualifications"and it meant to advance filter the Employees Authorisations Table.
Sub Filter_Crews()
Dim LastRow As Long, cel As Range
With Worksheets("Qualifications")
For Each cel In .Range("A3:O3")
If Len(cel) = 0 Then cel.ClearContents
Next
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
.Range("A6").Resize(LastRow - 5, 14).AdvancedFilter , Action:=xlFilterInPlace, _
CriteriaRange:=.Range("A2:O3")
End With
End Sub
Cell B2 on the Crew Allocation sheet is a drop down list comprising the values A, B, C, D, All.
Private Sub Worksheet_Change(ByVal Target As Range)
With Worksheets("Crew Allocations")
If Intersect(Target, Range("B2")) Is Nothing Then
Exit Sub
Else
With Worksheets("Qualifications")
Call Filter_Crews
End With
End If
End With
End Sub
The following code resides on the worksheet "Qualifications"and it meant to advance filter the Employees Authorisations Table.
Sub Filter_Crews()
Dim LastRow As Long, cel As Range
With Worksheets("Qualifications")
For Each cel In .Range("A3:O3")
If Len(cel) = 0 Then cel.ClearContents
Next
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
.Range("A6").Resize(LastRow - 5, 14).AdvancedFilter , Action:=xlFilterInPlace, _
CriteriaRange:=.Range("A2:O3")
End With
End Sub