Need Macro to Copy Visible Cells to New Sheet
 

set rng = ActiveSheet.Range("A1").CurrentRegion
rng.AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("Criteria"),
worksheets.Add
rng.EntireRow.copy Destination:=Activesheet.Range("A1")
rng.parent.activate

by default, only the visible rows should be copied.

Assume data table starts in A1.

However, if you record a macro for the Advanced filter, you will find that
copying the results to another page is a single entry in the multiargument
command

set rng = ActiveSheet.Range("A1").CurrentRegion
Worksheets.Add
Range("A1:M1").Value = rng(1).Resize(0,10).Value
rng.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("Criteria"), _
CopyToRange:=Activesheet.Range("A1")


above pseudo code is untested, but represents an approach.

Regards,
Tom Ogilvy


"Progster" wrote in message
...
Hi. I've recently added the use of 'Advanced Filter' to my bag-o-tricks,
and I find that what I would really like to do is to perform the AF "in
place", and then copy the visible cells to a new sheet.

I know it is possible to have the AF output it's results to another
(already existing) sheet, but I find this process cumbersome and
unintuitive.

Could it be that others have felt this way before me, and created a macro
which will grab visibile rows (the results of a filter operation), create
a
new sheet, and place a copy of those rows on the new sheet?

If so, I'd really appreciate it if one or more such solutions might show
up
here!