It is the whole macro, or at least what comprehensible, useful code you had. At the least, it should
replace:

Range("A7:U220").Select
Selection.AutoFilter
Selection.AutoFilter Field:=14, Criteria1:="y"
Range("A31:O42").Select
Selection.Copy
Dim rng As Range
With Cells
Sheets("Exits").Select
Set rng = .Range(.Cells(1, 1), .Cells(1, 1)).End(xlDown)
rng.Offset(1, 0).Select
Selection.PasteSpecial
End With

Code like this:

Sheets("Participant Worksheet").Select
Application.CutCopyMode = False
Selection.ClearContents

will depend on what the selection on "Participant Worksheet" is, which may lead to unexpected
results.... Much better would be specifying the range to clear:

Sheets("Participant Worksheet").Range("A1:B20").ClearContents


HTH,
Bernie
MS Excel MVP


wrote in message ps.com...
Thanks but I am not certain where to put it in the MACRO.



On Jun 21, 9:54 am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Range("A7:U220").AutoFilter Field:=14, Criteria1:="y"
Range("A31:O42").Copy _
Sheets("Exits").Cells(Rows.Count, 1).End(xlUp)(2)

Should do what you want...

HTH,
Bernie
MS Excel MVP



wrote in oglegroups.com...
I have been working with the MACRO below but I keep getting the error:
Select Method of Range Class Failed. when I run it.

What I have is data on one worksheet if that data equals a criteria it
is to go over to the EXITS worksheet. I had that working but the data
would just overwrite what was there before. I got some help with
having EXCEL look for the next blank line before writing new data but
now I get that error.

Any help?

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/21/2007 by System Administrator
'

'
Range("A7:U220").Select
Selection.AutoFilter
Selection.AutoFilter Field:=14, Criteria1:="y"
Range("A31:O42").Select
Selection.Copy
Dim rng As Range
With Cells
Sheets("Exits").Select
Set rng = .Range(.Cells(1, 1), .Cells(1, 1)).End(xlDown)
rng.Offset(1, 0).Select
Selection.PasteSpecial
End With

Sheets("Exits").Select
Range("A11").Select
ActiveSheet.Paste
Range("A13").Select
Sheets("Participant Worksheet").Select
Application.CutCopyMode = False
Selection.ClearContents
Selection.AutoFilter
Range("C2").Select
End Sub- Hide quoted text -

- Show quoted text -