Autofilter data from refreshable web query in one sheet based on criteria in another sheet

[cusoman]

Hello, have a problem I'm wracking my brains for here that I've searched high and low and haven't found a viable solution for. I've tried advanced filters and some VBA code snippets and nothing seems to be working.

I have a workbook that has a list of IDs in one worksheet that occupies column A, we'll call this sheet "ID List".

In another sheet, I have a refreshable web query (auto refreshes in the background and upon opening the file) showing the last 1000 results from an intranet site that outputs data from another application in html table form. We'll call this sheet "Monitor", and the column of interest here is Column K. What I need to do is this:

- Using the list of IDs in Column A in sheet "ID List" (there could be 500+ IDs here) I want to filter out unique rows of data, based on data in Column K of the “Monitor” sheet that contain any one of those IDs. The “contain” logical function here is important, as the ID will be in the middle of the data in the cells that the filter will be applied to.
- This autofilter needs to reapply to the data every time the web query is refreshed (thinking this part requires VBA for sure).

Example:

Column A on “ID List" sheet
1234
2345
3456
4567

Column K on “Monitor” sheet from refreshable web query (other columns omitted, but the data from those columns needs to come out of the filter as well)
TRANSACTION.S2345.RECORD
TRANSACTION.S2345.RECORD
TRANSACTION.S1111.RECORD
TRANSACTION.S1234.RECORD
TRANSACTION.S3456.RECORD
TRANSACTION.S4567.RECORD
TRANSACTION.S2345.RECORD
TRANSACTION.S9999.RECORD

When the filter is applied on the rows of data that contain the above samples using the ID criteria from “ID List”, it should only show the rows that have this data in column K in "Monitor":
TRANSACTION.S2345.RECORD
TRANSACTION.S1234.RECORD
TRANSACTION.S3456.RECORD
TRANSACTION.S4567.RECORD

So the duplicates of "2345" got filtered out, along with the data that didn't have IDs that matched the other sheet, namely "1111" and "9999".

Thanks in advance for any help that can be provided, it is greatly appreciated!

[cusoman]

Does the below code (that doesn't work) make sense to anyone what it is I'm trying to do? VERY wet behind the ears with my VBA, haven't used it in a long time, and this is my first foray into autofilters in VBA regardless.

Please Login or Register  to view this content.

[cusoman]

Alternatively, I considered using an Advanced Filter, but I can't get the Criteria to do the equivalent of "contains a value in this range of cells". For example, I am trying to enter for the advanced filter:

List Range: $A$1:$L$1000
Criteria Range: *('Cust Id'!$A$2:$A$500)*

This won't work, saying the "Reference is not valid" - is there a way to accomplish what I'm trying to do in the "Criteria Range" field with a proper statement there? If I can do it this way, then all I need to do is record the macro. Dying trying to figure this out...

[cusoman]

Don't mind me, just floundering all by myself here. Now trying to feed the range of values into an array to use in the filter and having issues. This try just gives me a "type mismatch" error. Would someone please chime in on this and help a brother out?

Please Login or Register  to view this content.