Copying filtered data to a new worksheet

[ceslyn]

I can't seem to know where to start with this.

I'm trying to copy the filtered information from cell b3(which ever cells appears) to AM6 to the last line on the bottom. I want to copy all the filtered information to the last entry on another page.

Any help would be appreciated.
Thanks!

 

[shyrath]

I took this from

http://www.contextures.com/xlautofilter03.html

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

 

[Jon von der Heyden]

Hello and welcome to MrExcel.com

Do you mean you want to do this with a macro? Which field / column are you placing the filter on and what is the criteria? And which sheet (name) do you want to paste it to?

Here's an example:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> FilterMoveExample()<br><br><SPAN style="color:#00007F">With</SPAN> Application<br>    .ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    .EnableEvents = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br><SPAN style="color:#00007F">With</SPAN> Sheets("Sheet1").Range("B2", Range("AM" & Rows.count).End(xlUp)) <SPAN style="color:#007F00">'filter range B2:AM last row (assumes column labels in row 2)</SPAN><br>    .AutoFilter field:=1, Criteria1:="MyCriteria" <SPAN style="color:#007F00">'filter by column B on criteria text "MyCriteria"</SPAN><br>    .Offset(1).SpecialCells(xlCellTypeVisible).Copy <SPAN style="color:#007F00">'copy the data excluding labels</SPAN><br>        Sheets("Sheet2").Range("B" & Rows.count).End(xlUp).Offset(1).PasteSpecial xlValues <SPAN style="color:#007F00">'paste to sheet2 on the next available row</SPAN><br>    .AutoFilter <SPAN style="color:#007F00">'turn filter off again</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br>Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[ceslyn]

I have tried with this VBA code from contextures.com but I cannot make it work.

The other code almost works.
I am doing a macro, there is only one field being filtered (colomn 2), the criteria is a "1" and i'm working on the worksheet QT2 and copying to Test 1. Line B2 is my title bar.

The whole process is almost working but it's only copying the 2 first lines to my other page.
Here is my code:

Sub FilterMoveExample()
With Application
.ScreenUpdating = False
.EnableEvents = False

End With
With Sheets("qt2").Range("B2", Range("AM" & Rows.Count).End(xlUp)) 'filter range B2:AM last row (assumes column labels in row 2)
.AutoFilter field:=2, Criteria1:="1" 'filter by column B on criteria text "MyCriteria"
.Offset(1).SpecialCells(xlCellTypeVisible).Copy 'copy the data excluding labels
Sheets("test 1").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues 'paste to sheet2 on the next available row
.AutoFilter 'turn filter off again
End With
Application.EnableEvents = True
End Sub

I really appreciate your help. Thank you!