Cut and Paste

[Chip Dukes]

I'm trying to write a simple cut and paste routine that triggers when
information is changed in a certain column ... the current row is supposed to
be cut then pasted into a seperate sheet in the same workbook ... see code
below (comments explain)

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 3 Then

'Select the current Row and Cut it
Rows(ActiveCell.Row).Cut

'Move to the catch sheet
Sheets("Catch Sheet").Select

'Find the first empty row
Range("A1").Select
Do Until Selection.Value = ""
Selection.Offset(1, 0).Select
Loop

'Select the row just found
Rows(ActiveCell.Row).Select

'Paste into the selected row
ActiveSheet.Paste

'Go back to the master sheet and clean up
Sheets("Test Sheet").Select
Selection.Delete Shift:=xlUp

Else
Exit Sub
End If

End Sub


The problem seems to be with line 12

Range("A1").Select

I am getting an error ... "select method of Range class failed" ...

Any suggestions.

Chip Dukes
(new to excel programming)

 

[FSt1]

hi
when using sheet code, you cannot select a range on another sheet.
in a way, sheet code restrick you to the sheet that owns the code.
but you can manipulate other sheets.
try this.........
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 3 Then
Target.EntireRow.Copy Destination:= _
Sheets("Catch Sheet").Range("A65000").End(xlUp).Offset(1, 0)
Target.EntireRow.Delete shift:=xlUp
Else
Exit Sub
End If
End Sub

Regards
FSt1

 

[Chip Dukes]

Nice ... very, very nice

Thank you

Chip

hi
when using sheet code, you cannot select a range on another sheet.
in a way, sheet code restrick you to the sheet that owns the code.
but you can manipulate other sheets.
try this.........
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 3 Then
Target.EntireRow.Copy Destination:= _
Sheets("Catch Sheet").Range("A65000").End(xlUp).Offset(1, 0)
Target.EntireRow.Delete shift:=xlUp
Else
Exit Sub
End If
End Sub

Regards
FSt1

 

[JLGWhiz]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr As Long
If Target.Column = 3 Then
lr = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
Target.EntireRow.Cut Sheets("Sheet2").Range("A" & lr + 1)
Target.EntireRow.Delete
Application.CutCopyMode = False
End If
End Sub

 

[FSt1]

you're welcome

Nice ... very, very nice

Thank you

Chip