ExcelBanter - Select Method of Range Class Failed

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Select Method of Range Class Failed (https://www.excelbanter.com/excel-programming/391775-select-method-range-class-failed.html)

Select Method of Range Class Failed

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

Select Method of Range Class Failed

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)

wrote in message
ups.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

Select Method of Range Class Failed

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 message ups.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

Select Method of Range Class Failed

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 -

Select Method of Range Class Failed

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 -