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.

Copy filtered range
 

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" wrote in message
...
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.

Copy filtered range
 

Thank you Mike, I'll try it.

"Mike Fogleman" wrote: