VBA: How can I use a string variable as autofilter criteria?

[John N. L.]

I'm attempting to use a list of values on one sheet as the auto-filter criteria on another sheet. The number of values on the list will change for each query. So, I'd like to be able to create a string variable that contains all of the values that I'd like to use as filter criteria, and then use this string variable in the autofilter statement. But, I'm not having luck with this approach. Looking for suggestions.

A more detailed description of my objective is: I have a number of category tabs, and each contains a list of records taken from a master list. Periodically, the masterlist gets updated, so I'd like to be able to use a key identifier (the list of values that I wish to use to create a string variable) to filter the new masterlist, then copy and paste the filtered record into an updated version of the category tab. This would be quicker than going line by line to update the records and color formats.

Thank you for any help.

 

[Abraham Andres Luna]

Can we see some of the code you have written? Maybe I can help a bit better if you posted it here.

 

[John N. L.]

I've tried a few variations of the below code, including use of an Array, but this should give you an idea of what I'm attempting to do. I just purchased a new Macro book for B&N, and it gives me an idea that I'll try tonight. The MasterList is maintained by a separate person, and periodically, I will get a copy to update my records. If I had access to the official Masterlist, I would just make links to the records that need to be updated. But, with the current system, I copy and paste the new MasterList into my spreadsheet, under a tab named "MasterList". I'm looking to update status information on my category tabs, and identify records that have been removed. I'm not sure that I will be able to automate the addition of new records. Right now, the record details are not entered consistently.

Dim crit As String
crit = Chr(34) & "20" & Chr(34) & "," & Chr(34) & "151" & Chr(34)

Sheets("MasterList").Select
ActiveSheet.Range("$A$1:$P$15233").AutoFilter Field:=1, Criteria1:=crit, Operator:=xlFilterValues

 

[Abraham Andres Luna]

So you want to compare your copy of MasterList with the new one? You should be able to use a SELECT statement on the new MasterList, excluding rows from your copy and that will give you the new rows you must had.

 

[John N. L.]

Rather than comparing each line to see if an update is needed for a given record or building the updated category list one record at a time, I figured that it would be a quicker execution time to just copy and paste the filtered results in the MasterList to a new copy of the category tab. The MasterList has tens of thousands of records. I'll look into application of the SELECT statement. It would have been nice if a variant string could be used to define an array in the Autofilter criteria.

There may not be a quick way to update the records, so it may need to be something updated over the weekend.