Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/Paste program error
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy paste error | Charts and Charting in Excel | |||
copy and paste error | Excel Discussion (Misc queries) | |||
copy-paste error | Excel Programming | |||
Code Error - Run Time Error 5 (Disable Cut, Copy & Paste) | Excel Programming | |||
Error in Copy/Paste | Excel Programming |