Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Autofilter question

  Asked By: Jawna    Date: Dec 04    Category: MS Office    Views: 828
  

I am trying to autofilter a large number of rows and then copy the
result of the filtering to a new range. I assumed that the following
code would do this nicely, but it doesn't.

It wants me to specify a range of cell equal to that resulting from
the filter operation rather than just the first cell where i want
the extracted info to start. Is there a way round this?

Sub Macro2()
Range("A1").CurrentRegion.AutoFilter _
Field:=3, Criteria1:="25.0000"
ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Rows.Copy _
Destination:=Range("AA2")
End Sub

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Stefan Thompson     Answered On: Dec 04

try this batch of code...found it on the site:
http://www.contextures.com/xlautofilter03.html

it also has a bunch of other code regarding autofilters...haven't really
read too deep into this page...but i hope it was what you needed...


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

 
Answer #2    Answered By: Ivan Coleman     Answered On: Dec 04

Destination range  can not accomodate origin range, i.e destination range is
from AA2 to lastcolumn, but origin range is the entire row.


ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Rows.Copy _
Destination:=Range("A1") <====

or

ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTypeVisible).Rows.Copy _ <<====
Destination:=Range("AA2")

Not tested.

 
Didn't find what you were looking for? Find more on Autofilter question Or get search suggestion and latest updates.




Tagged: