|
Cut and Paste
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) |
Cut and Paste
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) |
Cut and Paste
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) |
Cut and Paste
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" wrote in message ... 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) |
Cut and Paste
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) |
| All times are GMT +1. The time now is 03:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com