The information on using Specialcells is not required (although it can be
done that way - but why struggle). Looping is not required.

Activesheet.Autofilter.Range.Copy _
Destination:=worksheets("Sheet2").Range("A1")

will copy just the Visible cells in the autofiltered range.

The only caution is that if no rows meet the criteria and you do this, I
believe it copies the entire range - however, I assume you will not have
that situation.


If you only want one column

Sub AAA()
With ActiveSheet
Intersect(.Columns(3).Cells, _
.AutoFilter.Range).Copy _
Destination:=Worksheets("Sheet2").Range("A1")
End With

End Sub


If you want to pastespecial a column

Sub AAA()
With ActiveSheet
Intersect(.Columns(3).Cells, _
.AutoFilter.Range).Copy
End With
Worksheets("Sheet2").Range("A1") _
.PasteSpecial xlValues
End Sub

or the whole range:

Sub AAB()
With ActiveSheet
.AutoFilter.Range.Copy
End With
Worksheets("Sheet2").Range("A1") _
.PasteSpecial xlValues
Application.CutCopyMode = False
End Sub


--
Regards,
Tom Ogilvy



wrote in message news:bg8Td.19573$%U2.19162@lakeread01...
All -

I have a macro that creates a spreadsheet and saves if off as a tabbed
text file, and then autofilters it. The intent is to save the filtered
data off as a second, smaller tabbed text file. I need both.

So far my attempts have given me the same file contents with a different
name. Two ways forward occur to me, both of which I can't quite see how
best to do. And of course, I suspect strongly that there is a
third/fourth etc simple way to go that I don't at the moment see.

Way 1. Set a range equal to the what would be the autofiltered column
and, For Each cell, delete the entire row that doen't meet the criteria
that would have been specifed for that cell. My problem is that when
these occur in succession, I get every other one. It looks like the
cell's value will not be rechecked when a new cell "slides underneath."
The For Each loop appears to have satisfied itself with that cell and
does not recheck. How does one best deal with that? My fix would be to
cycle thru, count occurences, and wrap a "For Each/If bad Delete
Row/Exit For loop" in a larger loop done "count" times. Seems inelegant
though very do-able.

Way 2. Done manually, I select the columns, open a new spreadsheet, and
paste special values, and save off. I know how to do that
programatically save how to specify the ranges to copy.

Way 3. I don't know about yet.

Thoughts please.

...best, Hash