Saving AutoFiltered Data off as a smaller file?

[Hash]

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

 

[Guest]

Hi,
I am not sure if this is going to work for you or not, but it seems that
once the data is filtered, you need to "copy special/Visible Cells". Then get
to your new sheet and paste it? You have to select the area you want to copy,
prior to running the macro. This area will already be filtered.

Sub Macro1()
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
End Sub
Hope this helps. Thanks

 

[Ken Wright]

One way - Use Edit / Go To / Special cells / Visible Cells only - Copy then
Paste wherever you want to. Try recording that and you will get the syntax
you need to put it into your code such that you only copy what you see.

 

[Tom Ogilvy]

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

 

[Hash]

Tom -

As I awaited enlightenment, I coded my Way 1. Suffice it to say I was
right about the elegance and lack thereof.

What you proscribed worked like I knew it would. Thank you.

....best, Hash

 

[Hash]

Ken -
Thanks, but I don't seem to have as a special category Visible Cells
(running XL2000).

....best, Hash