| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
|
#2
Posted to microsoft.public.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) |
|
#3
Posted to microsoft.public.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) |
|
#4
Posted to microsoft.public.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) |
|
#5
Posted to microsoft.public.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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Paste and Paste Special No Longer Working - Excel 2003 | Excel Discussion (Misc queries) | |||
| Automating copy/paste/paste special when row references change | Excel Programming | |||
| Copy and Paste macro needs to paste to a changing cell reference | Excel Programming | |||
| How do I capture user paste action and convert to Paste Special | Excel Programming | |||
| Macro to Paste to specific line, and continue to Paste each time on next row not over | Excel Programming | |||
Linear Mode
