Copying filtered data to a new worksheet

ceslyn

New Member
Joined
Mar 4, 2009
Messages
48
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!
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

shyrath

Active Member
Joined
Jun 28, 2005
Messages
483
I took this from

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

Code:
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
 
Upvote 0

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,892
Office Version
  1. 365
Platform
  1. Windows
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>
 
Upvote 0

ceslyn

New Member
Joined
Mar 4, 2009
Messages
48
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!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,190,561
Messages
5,981,695
Members
439,730
Latest member
gjvv

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top