AutoFilter Question

[Michael Kintner]

I have made my selection:

Selection.AutoFilter Field:=1, Criteria1:=Range("Product_Name").Text

Now how do I copy that selection to a specific location?

Thank you in advance for your help!!

Mike

 

[John Wilson]

Mike,

The following assumes that A2 is in the range that has been filtered.

Range("A2").Select
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").select
ActiveSheet.Paste

John

 

[John Wilson]

Mike,

From a previous post by Tom Ogilvy (a better way):

Sub Tester1()
If ActiveSheet.AutoFilterMode Then
Set rng = ActiveSheet.AutoFilter.Range
rng.Copy Destination:=Worksheets("Sheet2").Range("A1")
Else
MsgBox "No filter in place"
End If
End Sub

John