I recently had to create a workbook for a client whereby a User facing worksheet had data validation cells that needed to be dynamically updated based on a prior selection.  To do this I thought I would try using Excel’s Advanced Filtering function.  The source data was in a Table, and I thought it would be quite easy to Filter the column of data and copy out the unique values to a hidden sheet, that would be the source of the data validation cell.

I was quite frustrated when Excel VBA kept responding to my AdvancedFilter command with the error “the extract range has a missing or illegal field name”

After a few moments of searching for this error and reviewing the data, I tried the filter manually, and Excel responded with the destination range must be on the active sheet.  So I thought I would try this in my code, and low and behold it worked.  So the VBA error message is completely off track!  Further testing showed that the copy unique values would only work if the destination range was on the same sheet as the source range.

I hope this helps others who have been frustrated by this error message!