Move Table Row to Another Sheet

[jessebh2003]

Hello,

I'm working on a table and need to automatically move a row to another table on another sheet.

Book1
	A	B	C	D	E	F	G		I	J	K	L
1				E	F	G	H					Rev 1_0
2		Project Impact Tool (Six Sigma Tool)		From 1 to 5 1 = Low; 3 = Medium; 5 = High
3	Proj. ID:	Project Title	What problem are we trying to solve? What is the business impact we are targeting?	Project Scale	Ease (Effort) of Implementation	Quality Impact	Risks		Rank / Priority	Complete	LBC Assigned to	Due Date
4	1	De-escalation	Reduce seclusion hours, staff assults, and restraint usage	5	5	5	5		1		Christina	ongoing
5	2	PMH	Orient associates to the new PMH, align workflows with multiple departments, assess learning needs of associates working at the new PMH.	5	5	5	5		1		Kathy	12/31/2023
6	3	NAO redesign	Improve Culture and retention	5	5	4	3		3		Kristin	1/28/2022 ongoing
Sheet1

Cell Formulas
Range	Formula
I4:I6	I4	=IF(H4=" "," ",RANK(H4,PriorityNumbers1))
A4	A4	=ROW(A1)
A5:A6	A5	=$A4+1

Named Ranges
Name	Refers To	Cells
PriorityNumbers1	=Sheet1!$H$4:$H$63	I4:I6

Cells with Data Validation
Cell	Allow	Criteria
D3	Any value
E3	Any value
F3	Any value
G3	Any value
D4	List	1,2,3,4,5
E4:G4	List	1,2,3,4,5
D5:G6	List	1,2,3,4,5
J1:J3	Any value
J4:J6	List	Y,N
I3	Any value

I found a similar post on here (Automatically Move Row to Another Sheet Based On Cell Value) and updated the code that @Joe4 provided to match my table; however, the row isn't being pasted into the other table.

Private Sub Worksheet_Change(ByVal Target As Range)

'   Check to see only one cell updated
    If Target.CountLarge > 1 Then Exit Sub
    
'   Check to see if entry is made in column J after row 4 and is set to "Y"
    If Target.Column = 10 And Target.Row > 4 And Target.Value = "Y" Then
        Application.EnableEvents = False
'       Copy columns A to L to complete sheet in next available row
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "L")).Copy Sheets("Completed Prioritization Tool").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
'       Delete current row after copied
        Rows(Target.Row).Delete
        Application.EnableEvents = True
    End If
    
End Sub

My data is in Table1 on sheet Example Prioritization Tool and I want to move it to Table 2 on sheet Completed Prioritization Tool. Appreciate any guidance. Thanks!

 

[psgoldberg]

Target.Row > 3 to get the first row. Otherwise works as advertised.

 

[jessebh2003]

Target.Row > 3 to get the first row. Otherwise works as advertised.

Hi, I changed that to 3 but the row didn't get pasted onto the other sheet - it just disappeared.

 

[jessebh2003]

@psgoldberg, I made that change and the row disappears without being pasted onto the Completed sheet. Any ideas? @My Aswer Is This - I saw your solution on Move a row to another sheet based on data manually changed - Help - on the verge of tears and thought you may be able to weigh in. Appreciate any insight. Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)

'   Check to see only one cell updated
    If Target.CountLarge > 1 Then Exit Sub
    
'   Check to see if entry is made in column J after row 4 and is set to "Y"
    If Target.Column = 10 And Target.Row > 3 And Target.Value = "Y" Then
        Application.EnableEvents = False
'       Copy columns A to L to Completed sheet in next available row
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "L")).Copy Sheets("Completed").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
'       Delete current row after copied
        Rows(Target.Row).Delete
        Application.EnableEvents = True
    End If
    
End Sub

 

[psgoldberg]

I copied your data into a new workbook, named the range, created a worksheet named <<Completed Prioritization Tool>> and put in a value in column H and the macro worked in that a row was copied to the new sheet and the row deleted from Sheet1. Is the workbook named correctly? Is there anything in Column A below where you are looking? Did you step through the macro to see that all commands are issued?

Post the sheet on Dropbox and we can take a look.

 

[My Aswer Is This]

I posted the forementioned post in Post #4

But my post had nothing to do with A Table

You said in Post 1:
I'm working on a table and need to automatically move a row to another table on another sheet.

Are you sure you're working with an Excel Table

If you're wanting to work with a sheet change event script like mine in Post 4
Please give me specific details about what you're attempting to achieve
And how do you want the script activated

 

[jessebh2003]

Thanks so much for your help. It's working now. Appreciate your assistance.