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)

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" wrote:

> 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)

Nice ... very, very nice

Thank you

Chip

"FSt1" wrote:

> 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" wrote:
>
> > 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)

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




"Chip Dukes" <(E-Mail Removed)> wrote in message
news:372EFD9C-B3F9-4688-853C-(E-Mail Removed)...
> 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)

you're welcome

"Chip Dukes" wrote:

> Nice ... very, very nice
>
> Thank you
>
> Chip
>
> "FSt1" wrote:
>
> > 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" wrote:
> >
> > > 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)