Advanced Filter only works if the worksheet is active

[Rfriend]

Hello,

I have 3 advanced filters for various tables on a single tab that return data to the same user form. They all work fine if I am physically on the worksheet that the advanced filters reside on. If I run the user form while I have another tab open the advanced filters return "Nothing Found".

Advfilter1 searches an employee database, on a seperate worksheet, and returns active or inactive positions to listbox1.
A double click in listbox1 places the selections job code into a txtbox.
A command button then activates advfilter2 to search for job codes which will be considered as potential replacements and returns that list to listbox2.
At the same time advfilter3 searches the employees database and returns a list of available personnel to listbox3 that possess any of the listed job codes in listbox2.

Why does only 1 of the 3 (if any) work if not physically on the specific worksheet, but all work if the active worksheet is selected?

Private Sub lstBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim i As Integer
    Dim JDOutSH As Worksheet
    Set JDOut = Sheet14
    On Error Resume Next
        i = Me.lstBox1.ListIndex
            Me.CS1.Value = Me.lstBox1.Column(0, i)
            Me.CS2.Value = Me.lstBox1.Column(3, i)
        On Error GoTo 0
End Sub

Sub AdvFilterVaclstBox1()
    Sheets("Staff_Data").Range("Table735[#All]").AdvancedFilter Action:= _
        xlFilterCopy, CriteriaRange:=Range("VacantOut!Criteria"), CopyToRange:= _
        Range("D6:P6"), Unique:=False
End Sub

Sub AdvFilterVaclstBox2()
    With Sheet14
    Range("Table297[#All]").AdvancedFilter Action:=xlFilterCopy, CriteriaRange _
        :=Range("C6:C7"), CopyToRange:=Range("D6:P7"), Unique:=False
    End With
        Range("D7:P7").Select
            Selection.Copy
        Range("V11").Select
            Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
End Sub

Sub AdvFilterVaclstBox3()
    Sheets("Staff_Data").Range("Table735[#All]").AdvancedFilter Action:= _
        xlFilterCopy, CriteriaRange:=Range("JDOut!Criteria"), CopyToRange:=Range( _
        "V27:AN27"), Unique:=False
End Sub

Private Sub cmdSearchStatus_Click()
    Dim Status As Variant
        On Error GoTo errHandler:
        lstBox1.RowSource = ""
    Set Status = cboSearchStatus
        Application.ScreenUpdating = False
    If Me.cboSearchStatus.Value = "Active" Or Me.cboSearchStatus.Value = "Inactive" Or Me.cboSearchStatus.Value = "Vacant" Then
     Sheet3.Range("D5").Value = Me.cboSearchStatus.Value
     AdvFilterVaclstBox1
    If Sheet3.Range("C9").Value = "" Then
        lstBox1.RowSource = ""
    Else
        lstBox1.RowSource = "VacantOut"
    End If
        Exit Sub
    End If
        AdvFilterVaclstBox1
    If Sheet3.Range("C9").Value = "" Then
        lstBox1.RowSource = ""
    Else
      lstBox1.RowSource = "VacantOut"
    End If
      On Error GoTo 0
    Exit Sub
errHandler:
    MsgBox "No match found for " & cboSearchStatus.Value
    Me.lstBox1.RowSource = ""
    Exit Sub
End Sub

Private Sub cmdJDLookup_Click()
    Dim JobClass As Long
    Dim JDOutSH As Worksheet
    Dim Staff_Data As Worksheet
        On Error GoTo errHandler:
    Set JDOutSH = Sheet14
    Set Staff_Data = Sheet7
    JobClass = Me.CS2.Value
        JDOutSH.Range("C7").Value = Me.CS2.Value
    Application.ScreenUpdating = False
        lstBox2.RowSource = "JDSrchNew"
        'Transpose
    With Sheet14
        If CS2.Value = "" Then
            .Range("C7").Value = ""
        Else
            .Range("C7").Value = CS2
        End If
    End With

        If Sheet14.Range("C7").Value = "" Then
            lstBox2.RowSource = ""
        Else
            lstBox2.RowSource = "JDSrchNew"
        End If
        
        If Sheet14.Range("C7").Value = "" Then
            lstSelector.RowSource = ""
        Else
            lstSelector.RowSource = "PotentialStaff"
        End If
            AdvFilterVaclstBox2
                AdvFilterVaclstBox3
                   lstBox2.RowSource = Sheet14.Range("JDSrchNew").Address(external:=False)
                lstSelector.RowSource = Sheet14.Range("PotentialStaff").Address(external:=True)
        Exit Sub
errHandler:
    MsgBox "No match found for " & txtSearchTrng2.Text
        On Error GoTo 0
    Exit Sub
End Sub

 

[rlv01]

Because when you use unqualified ranges, as you are doing in many places in your code, the 'parent' worksheet is assumed to be the ActiveSheet object. So for example in the Sub below,

Sub AdvFilterVaclstBox1()
    Sheets("Staff_Data").Range("Table735[#All]").AdvancedFilter Action:= _
        xlFilterCopy, CriteriaRange:=Range("VacantOut!Criteria"), CopyToRange:= _
        Range("D6:P6"), Unique:=False
End Sub

CopyToRange:=Range("D6:P6") is unqualified and is the same as writing CopyToRange:=ActiveSheet.Range("D6:P6")

If you want it to act on worksheet "Staff_Data", you have to explicitly refence it:

Sub AdvFilterVaclstBox1()
    Sheets("Staff_Data").Range("Table735[#All]").AdvancedFilter Action:= _
        xlFilterCopy, CriteriaRange:=Sheets("Staff_Data").Range("VacantOut!Criteria"), CopyToRange:= _
        Sheets("Staff_Data").Range("D6:P6"), Unique:=False
End Sub

or

Sub AdvFilterVaclstBox1()
    With Sheets("Staff_Data")
    .Range("Table735[#All]").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=.Range("VacantOut!Criteria"), _
        CopyToRange:=.Range("D6:P6"), Unique:=False
    End With
End Sub

Your should review your code for other instances of unqualified ranges.

 

[Rfriend]

Thank you so much. My skill level is apparent so I really appreciate the knowledge.