Copy/Paste program error

[Eager2Learn]

Hello,
I have run into a slight problem with the following code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 And Target.Count = 1 Then
Select Case Target.Value

Case "COMPLETED"
Dim rng As Range
Set rng = Sheets("COMPLETED").Range("A"
Rows.Count).End(xlUp).Offset(1, 0)
ActiveCell.EntireRow.Copy
rng.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.EntireRow.Delete

Case "FALL OUT"
Set rng = Sheets("Fall Outs").Range("A"
Rows.Count).End(xlUp).Offset(1, 0)
ActiveCell.EntireRow.Copy
rng.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
With Cells(ActiveCell.Row, 1)
.Offset(0, 17 - 1).Resize(1, 12).ClearContents
.Offset(0, 22).Formula = "=AJ" & .Row
.Offset(0, 23).Formula = "=AK" & .Row
.Offset(0, 24).Formula = "=AL" & .Row
.Offset(0, 25).Formula = "=AM" & .Row
.Offset(0, 26).Formula = "=AN" & .Row
.Offset(0, 27).Value = ""
.Offset(0, 4).Value = Date
.Value = "1-OPEN"
End With

Sheets("Fall Outs").Activate
Cells(rng.Row, 42).Select <---where ERROR occurs
Exit Sub

Case ""
Exit Sub

Case Else

End Select
End If
End Sub


The error comes near the bottom with: Cells(rng.Row, 42).Select

What gives? Before I put this into the Worksheet.change/case, i
worked fine.

Thanks in advance,
E2

 

[Tom Ogilvy]

Change
Sheets("Fall Outs").Activate
Cells(rng.Row, 42).Select <---where ERROR occurs
Exit Sub


to

Sheets("Fall Outs").Activate
Sheets("Fall Outs").Cells(rng.Row, 42).Select
Exit Sub

An unqualified range reference in a sheet module refers to the sheet
containing the module. So with Fall Outs active, you are trying to select a
range that is not on the active sheet.

 

[Eager2Learn]

This worked PERFECT...thanks for the info