Paste Issue when AutoFilter Results are Consecutive

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

Please Login or Register  to view this content.

It's hard to diagnose without seeing your data format, including an example of the prob

My individual data fields are derived from one large field through a series of left(), Right(), Find() commands.

I have included an example of the problem. (see attch) The states in RED will not work because the are consecutive when Autofiltered. Those w/ a green shade will work because there is a 'break' in the sequence.

Thanks for cleaning that up. I am very new to VBA and don't know which commands can be consolidated into one line AND I like the fact that my screen does 'flicker' from all the windows jumping around.

Only thing with using 'PasteSpecial' is that if there is no State, it will copy the entire contents to that State's tab.

I added a tab called 'Stats' and do a COUNTIF on field 3 and added a IF statement in the code. Works like a Champ!

If Sheets("STATS").Range("b2").Value <> 0 Then
ActiveSheet.Range("$A$1:$H$500").AutoFilter Field:=3, Criteria1:="OHIO"
ActiveSheet.Range("D2:H500").Copy
Sheets("OHIO").Range("A2").PasteSpecial xlValues
End If

Thanks for your help!

DG

I'm glad you were able to find a workable solution.