Copy/Paste program error
 

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

Copy/Paste program error
 

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.

--
Regards,
Tom Ogilvy


"Eager2Learn " wrote in message
...
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, it
worked fine.

Thanks in advance,
E2L


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

Copy/Paste program error
 

This worked PERFECT...thanks for the info

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