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

--
Message posted from http://www.ExcelForum.com