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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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
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
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,218,627
Messages
6,143,584
Members
450,494
Latest member
GolfNut39

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
Back
Top