My code (below) will Autofilter on the second field, Select the results, copy/paste to another worksheet.

This works great UNLESS the results of the Autofield is a consecutive range. (i.e. single Row or multiple Consecutive Rows) If that is the case, I receive a #REF for all entires. ((If there were 14 consecutive rows, then I will have 14 rows of #REF))

For Example:
32 OHIO data (Multiple consecutive rows)
33 OHIO data
34 OHIO data

or

32 OHIO data (Single 'consecutive' row)

If there is a break in the results, it works fine. ((i.e. 2, 32, 33, 34))


I've tried specialcells, visible cells, etc and nothing works.


Has anyone seen this or have an idea how to fix it. (what's going on?)

Thanks,

DG



    ActiveSheet.Range("$A$1:$E$500").AutoFilter Field:=2, Criteria1:="OHIO"
    ActiveSheet.Range("C2:E500").Select
    Selection.Copy
    Sheets("OHIO").Select
    ActiveSheet.Range("A2").Select
    ActiveSheet.Paste