Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy filtered range
Thank you Mike, I'll try it.
"Mike Fogleman" wrote: |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy only filtered range. | Excel Discussion (Misc queries) | |||
Copy filtered data | Excel Discussion (Misc queries) | |||
How to copy a filtered range ? | Excel Discussion (Misc queries) | |||
Copy filtered data | Excel Discussion (Misc queries) | |||
traversing through a filtered range based on another filtered range | Excel Programming |