Three-way Filter

Hi, I'm a newbie and was wondering if someone would kindly help me with the following?

In the attached workbook named: Test 2.xls please view the worksheet named: Report. On this worksheet the user (user has very little experience in Excel 2003) selects an Operation in cell B2 via a drop-down list and a Task Group in cell B4 via a drop-down list.

After the user has made their selections, I need Excel 2003 to automatically Select worksheet named: Data. I then need it to automatically filter the user selected Operation in column B and the user selected Task Group in column C.

Once the filtering is complete, I need Excel to automatically lookup any instances where the user selected Operation and user selected Task Group has a matching value in column X containing the text: "VACANT".

For example: if the user selected the Operation: "Seaside" and Task Group: "Finance", then I need these two selections to be filtered on worksheet named: Data. After that, I need Excel to automatically lookup or filter any instances of a matching value in column X containing the text: "VACANT".

If there are any instances of a matching value in column X containing the text: "VACANT", I need Excel to automatically select that entire row of data and copy it to a new workbook named: Report.xls on worksheet named: Report.

Report.xls resides on C:\ drive.

Operations has a defined name = Operations!$A$2:$A$12.

Task Group has a defined name = Groups!$A$2:$A$29.

Any help would be greatly appreciated.

Kind regards,

Chris

Maybe something like this.

Sub X()

    Dim rngOperation As Range
    Dim rngTaskGroup As Range
    Dim rngData As Range
    Dim wbkReport As Workbook
    
    Set rngOperation = Worksheets("Report").Range("B2")
    Set rngTaskGroup = Worksheets("Report").Range("B4")
    
    Set rngData = Worksheets("Data").UsedRange
    Set rngData = rngData.Offset(1, 0).Resize(rngData.Rows.Count - 1)
    rngData.Parent.Unprotect
    
    rngData.AutoFilter 2, rngOperation
    rngData.AutoFilter 3, rngTaskGroup
    rngData.AutoFilter 24, "VACANT"
    
    If rngData.SpecialCells(xlCellTypeVisible).Cells.Count > rngData.Columns.Count Then
        Set wbkReport = Workbooks.Add
        rngData.SpecialCells(xlCellTypeVisible).Copy wbkReport.Worksheets(1).Range("A1")
        wbkReport.SaveAs "C:\Report.xls"
        wbkReport.Close False
    End If
    
    rngData.AutoFilter
    rngData.Parent.Protect
    
    Set wbkReport = Nothing
    
End Sub