PDA

View Full Version : Solved: Setting criteria field on autofilter



MBradley1982
10-30-2007, 05:20 AM
I have two tabs:
1) search criteria
2) archive list

I want to type a reference in a cell on tab 1 (cell is named REF) and then autofilter tab 2 by this data.

I cannot get the criteria on the macro to change to be the reference I have typed in it remains a constant.

The macro code I have generated is below:

Application.Goto Reference:="REF"
Selection.Copy
Sheets("File_Locations").Select
Selection.AutoFilter Field:=1, Criteria1:="AA0020"", Operator:=xlAnd"
Application.CutCopyMode = False

Any help will be much appreciated

Thanks

MB

Simon Lloyd
10-30-2007, 05:30 AM
Here's a little code that works

Sub FilterIt()
Sheets("Sheet2").Select
Columns("B:C").Select'the range i set autofilter on
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=Sheets("Sheet1").Range("A1").Value'the cell to look at for the filter criteria
End Sub

Simon Lloyd
10-30-2007, 05:33 AM
This code by Tom Ogilvy copies your filtered data from the active sheet to sheet3!

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("Sheet3").Cells.Clear
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
Destination:=Worksheets("Sheet3").Range("A1")
End If
ActiveSheet.ShowAllData

End Sub

MBradley1982
10-30-2007, 05:40 AM
Works great,

Many thanks

MB

Simon Lloyd
10-30-2007, 06:28 AM
Glad i could help!, please mark your thread solved by using the thread tools at the top of this window.

toyguy9600
11-08-2007, 03:25 PM
Anyone know of a way to confirm that their is at least one line that met the criteria of the filter. I've screwed up data because none of the cells fit a certain criteria and my macro would than paste the incorrect info to every cell in the range when it shouldn't have done anything.

Simon Lloyd
11-08-2007, 05:09 PM
Toyguy9600 welcome to VBAX, in order for you to recieve a solution to your question you should start a new thread as this one is marked Solved and people don't usually revisit a solved thread, that said if you look at my last post where i posted some code provided by Tom Ogilvy it has this section If rng2 Is Nothing Then
MsgBox "No data to copy"
which accounts for the fact that there was nothing to copy, all i can imagine is if you used the code above you have a problem with the range you set.