Copying a filtered list

W

wmjenner

I have a list of filtered data. I want to use a macro to copy that lis
to another sheet. Is there a macro command or code that I can use t
copy just to the last line of data in the list or do I have to cop
enough blanks to make sure I have captured all the data (the list wil
grow over time). I would like to just go to the exact bottom of th
data because I want to then take that same filtered list, copy it t
another worksheet and then average the columns. If I include blan
lines, it seems like they would be averaged too (as zeroes) which woul
distort the results. Any ideas?

Thanks for any brilliant ideas
 
D

Dave Peterson

The default behavior of copying the autofilter range is to just copy the visible
cells (xl97 and above).

So you could copy the whole range:

Option Explicit
Sub testme()

'copy the whole range, including the headers
With Worksheets("sheet1")
.AutoFilter.Range.Copy _
Destination:=Worksheets("sheet2").Range("a1")
End With

'don't copy the headers
With Worksheets("sheet1")
With .AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count = 1 Then
'do nothing--only headers visible
Else
.Resize(.Rows.Count - 1).Offset(1, 0).Copy _
Destination:=Worksheets("sheet3").Range("a1")
End If
End With
End With


End Sub
 
M

Max

.. I would like to just go to the exact bottom of the
data because I want to then take that same filtered list, copy it to
another worksheet and then average the columns. If I include blank
lines, it seems like they would be averaged too (as zeroes) which would
distort the results...
Click to expand...

To get the averages, you could also try say, inserting a new row1
(right at the top) and put in this row1, say in A1, something like:

=AVERAGE(IF(A3:A1000>0,A3:A1000))

which is array-entered (with CTRL+SHIFT+ENTER)

The above assumes the data in col A starts in row3 down

"A1000" is an arbitrary last cell in col A, assumed to be large enough
to cover the max likely number of rows with data in col A
(Adjust to suit)
 
W

wmjenner

Thanks for two great suggestions. Max's is simpler so I'll try tha
first. I hadn't thought about manipulating data from the "target
end. Must be what "think outside the box" means
 
Top