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.
|