Triggering a Filter using VBA

[elio_xh]

I'm creating a macro that automatically creates a Gantt chart and fills in the procedure steps. Since the object I'm tracking is standardized, I have a large database that contains all part numbers and the corresponding procedure steps. My macro first takes in user input to enter the part number, and I'd like it to trigger the filter using the variable that stores the part number.

I've used this code:

    ActiveSheet.ListObjects("Tracker_AllActiveJobs").Range. _
        AutoFilter Field:=2, Criteria1:=NewPartName

The main problem with this code is that it works fine if there is only one part in the database that matches the "NewPartName" variable, but the majority of the time there will be more than 1 part, with different suffixes or prefixes. So ideally I'd like the filter to search for all parts that have the variable within it's full title.

There was also a second way I could trigger the filter. I created a function so that if you enter the part number in a certain cell and press enter, it would trigger the filter. Here's the code for that:

Range("B2").Select
    ActiveCell.FormulaR1C1 = NewPartName

The variable "NewPartName" is a string, and when I run the macro it launches the debugger and stops on the the "ActiveCell.FormulaR1C1 = NewPartName" line.

If someone can help me find a way to trigger the filter, that would be awesome. I have no preference to using either of the formulas I've posted, so what ever is easier for you to work with I will implement.