Copy filtered range

Hi all,

In VBA, if I auto filter a range and than do a simple copy and paste into
another worksheet only the filtered values are copied. That is good. But if I
do the same but use arrays to transfer the filtered worksheet than everything
gets transferred, even the stuff that I have filtered. Since I prefer to use
arrays rather than copy and paste, how is this done so that only the filtered
values are captured?

Roughly the non-working code I use is as follows.

Selection.AutoFilter Field:=3, Criteria1:=something
LastRow = LastRowInColumn("A")
tempArr = Range("A1:H" & CStr(LastRow)).Value
Sheets(something).Select
Range("A1:H" & CStr(LastRow)).Value = tempArr

Thanks for any tips.
Milos.

This not with arrays, but it copies filtered data....from Tom Olgilvy

Sub CopyFilter()
'by Tom Ogilvy
Dim rng As Range
Dim rng2 As Range

With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No data to copy"
Else
Worksheets("Sheet2").Cells.Clear
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
Destination:=Worksheets("Sheet2").Range("A1")
End If
ActiveSheet.ShowAllData

End Sub

Mike F
"Milo" <[email protected]> wrote in message
news:[email protected]...
> Hi all,
>
> In VBA, if I auto filter a range and than do a simple copy and paste into
> another worksheet only the filtered values are copied. That is good. But
> if I
> do the same but use arrays to transfer the filtered worksheet than
> everything
> gets transferred, even the stuff that I have filtered. Since I prefer to
> use
> arrays rather than copy and paste, how is this done so that only the
> filtered
> values are captured?
>
> Roughly the non-working code I use is as follows.
>
> Selection.AutoFilter Field:=3, Criteria1:=something
> LastRow = LastRowInColumn("A")
> tempArr = Range("A1:H" & CStr(LastRow)).Value
> Sheets(something).Select
> Range("A1:H" & CStr(LastRow)).Value = tempArr
>
> Thanks for any tips.
> Milos.

Thank you Mike, I'll try it.

"Mike Fogleman" wrote: