Select Method of Range Class Failed

[TGerety]

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

 

[Bob Phillips]

Not tested it, burt Range is not a property of Cells. Maybe try this

With Sheets("Exits")
Set rng = .Range(.Cells(1, 1), .Cells(1, 1)).End(xlDown)
rng.Offset(1, 0).PasteSpecial
End With

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bernie Deitrick]

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

 

[TGerety]

Thanks but I am not certain where to put it in the MACRO.

 

[Bernie Deitrick]

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

Code like this:

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