Move cells to another sheet based on criteria + Auto-sort

[ebcopystop]

Hello,

Brand new to VBA so bare with me.

I found a separate thread that almost solved my problem, but have a couple caveats needing attention.

I used the below code to add a row from my "TO DO" sheet to my "DONE" sheet if "Done" was entered into any cell in column K. The adds the "Done" row to the bottom of the DONE sheet and removes it from TO DO. I run into a problem, though, when I try to insert a row on "TO DO" -- I get the Type Mismatch debug error on 'If Target.Value = "Done" Then'.

1. I could eliminate this all together if I could figure out how to auto-sort by date (Column A) once a date is added
2. I have 2 header rows that would not be included in the sort

Bonus question:
Is there a way to make the DONE sheet auto sort by date (Column A) once the new row has been added?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
If Not Intersect(Target, Range("K:K")) Is Nothing Then
If Target.Value = "Done" Then
'Copy row to "DONE" sheet and delete row
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
LR = Sheets("DONE").Range("A" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy Destination:=Sheets("DONE").Range("A" & LR)
Target.EntireRow.Delete shift:=xlUp
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End If
End If
End Sub

 

[Michael M]

Does this do it...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
If Not Intersect(Target, Range("K:K")) Is Nothing Then
If Target.Value = "Done" Then
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
LR = Sheets("DONE").Range("A" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy Destination:=Sheets("DONE").Range("A" & LR)
Target.EntireRow.Delete shift:=xlUp
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End If
End If
With Worksheets("DONE")
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("A3"), SortOn:=xlSortOnValues, Order:=xlDscending, DataOption:=xlSortNormal
    With Worksheets("DONE").Sort
        .SetRange Range("A3:K" & Cells(Rows.Count, "A").End(xlUp).Row)
        .Header = xlNo
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With
End Sub

 

[ebcopystop]

Does this do it...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
If Not Intersect(Target, Range("K:K")) Is Nothing Then
If Target.Value = "Done" Then
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
LR = Sheets("DONE").Range("A" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy Destination:=Sheets("DONE").Range("A" & LR)
Target.EntireRow.Delete shift:=xlUp
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End If
End If
With Worksheets("DONE")
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("A3"), SortOn:=xlSortOnValues, Order:=xlDscending, DataOption:=xlSortNormal
    With Worksheets("DONE").Sort
        .SetRange Range("A3:K" & Cells(Rows.Count, "A").End(xlUp).Row)
        .Header = xlNo
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With
End Sub

Nice! You definitely fixed the insert issue, I'm stoked about that.

The sorting doesn't seem to be triggering

 

[ebcopystop]

Welll..... it worked for a little while. Without me changing the code, it stopped working. Since then, I re-formatted some things, tried to update the VBA myself, yadda yadda -- and here's where I'm at:

1. I removed the 2nd "top" row - so now I only have one header row
2. When I type "Done" in column K, the row is removed from the "TO DO" sheet, and added to the bottom of the "DONE" sheet, but then it sorts rows A2 - A15 only. My guess is because I only have A2-A15 populated on the "TO DO" sheet (???). (I currently have about 50 rows on "DONE")

Some other things to note that may or may not be affecting the outcome:

1. There are 3 differences between the "TO DO" sheet and the "DONE" sheet:

a. A1 text is "TO DO Date" on "TO DO", and "DONE Date" on "DONE"​

b. Columns B and C are hidden on "DONE"​

c. On the "TO DO" sheet, there is Conditional Formatting in Column A to highlight cells different colors based on date. On the "DONE" sheet, there is Conditional Formatting to remove the Conditional Formatting brought over from the "TO DO" sheet.​

​

2. There is a formula in row L1 to show the current date

3. There is a formula in Column B to populate the day of the week based on the date in Column A (which is then conditionally formatted based on the current date in L1)

I uploaded a mini-sheet of the "TO DO" sheet - hope it works, and helps.

Skedge.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	TO DO Due Dt	Day	Time	Task	PBI #	PBI	Program	Client	Effort	References	Status	01/06/2022
2	ON HOLD	ON HOLD		Test	293289	CBS - GNF - CCA and READ - 3 BMP C/C RDP program modifications	SLIVXFK	GNF	8		On hold
3	01/05/2022	Wednesday	9:00 AM	TDI Extract Processing	324716	TDI - GSMR - JAN 5 - TDI extract Nelnet - PROD	TDI	NHS, NHD	2	Confluence	In progress
4	01/05/2022	Wednesday		Test	340183	CBS - SLS - BUG - remove CBS Comaker - Letter Print	SLEEUPK	SLS	8		In progress
5	01/06/2022	Thursday	9:00 AM	TDI Extract Processing	324719	TDI - EFS and EFD - JAN 6 - TDI extract Nelnet - PROD	TDI	EFS, EFD	3		Done
6	01/06/2022	Thursday	11:00 AM	Log in	324826	TDI - ALL - SPIKE - Update IRS Filing Schedule - January 4	TDI	--	1	Confluence	To Do
7	01/06/2022	Thursday	9:00 AM	Run email marking (Update)/MFT release	330129	TDI - EFS and EFD - JAN 3 and 6 - Email marking - PROD - BEFORE Tax Extract	TDI	EFS, EFD	2		To Do
8	01/06/2022	Thursday	10:00 AM	Date change	334652	TDI - Den/JAX/GNF - JAN 1 and 6 - Tax Letter 0791 On/Off and Last Print Date	TDI	GNF, DEN, JAX	1	Confluence	To Do
9	01/06/2022	Thursday	9:00 AM	Update Tax Ready text PROD	324773	TDI - EFD - Jan 6 - Statements with 'Tax Ready' - January Prod	TDI	EFD	2		To Do
10	01/06/2022	Thursday	9:00 AM	TDI Extract Processing	324713	TDI - OSLA - JAN 6 - TDI extract Nelnet - PROD	TDI	OKD	3		To Do
11	01/06/2022	Thursday	9:00 AM	Update Tax Ready text PROD	324777	TDI - EFS - Jan 6 - Statements with 'Tax Ready' - January Prod	TDI	EFS	2		To Do
12	01/06/2022	Thursday	9:00 AM	Update Tax Ready text PROD	324780	TDI - OKD - Jan 6 - Statements with 'Tax Ready' - January Prod	TDI	OKD	2		To Do
13	01/06/2022	Thursday	9:00 AM	Generate tax letters 0791	324784	TDI - NHS - PROD - Generate Tax Letter (0791) - January 6	TDI	NHS	3		To Do
14	01/06/2022	Thursday	9:00 AM	Generate tax letters 0791	324786	TDI - NHD - PROD - Generate Tax Letter (0791) - January 6	TDI	NHD	3		To Do
15	01/06/2022	Thursday	9:00 AM	TDI Extract Processing	324704	TDI - NN - JAN 6 - TDI extract Nelnet - PROD	TDI	GNF, DEN, JAX	3		To Do
TO DO

Cell Formulas
Range	Formula
L1	L1	=TODAY()
B2:B15	B2	=TEXT(A2,"dddd")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1,A2:B1048576	Dates Occurring	yesterday	text	NO
A1,A2:B1048576	Dates Occurring	today	text	NO
A1,A2:B1048576	Dates Occurring	tomorrow	text	NO
A1,A2:B1048576	Dates Occurring	this week	text	NO

 

[ebcopystop]

Here's a mini-sheet of the "DONE" sheet

Skedge.xlsm
	A			D	E	F	G	H	I	J	K	L
1	DONE Due Dt			Task	PBI #	PBI	Program	Client	Effort	References	Status	01/06/2022
2	01/04/2022			Test	340982	TDI - NDS - IAP - remove OID on PIF loans	SLH3CPP	NDS	5
3	01/04/2022			Test	340832	TDI - GNF - IAP - remove OID on PIF loans	SLH3CPP	GNF	5
4	01/03/2022			Missing CQ Overlapping	324529	TDI - Jan 3 - Overlapping transactions query - Prep - January	TDI	EFS, EFD, NHS, OKD	2
5	01/03/2022			Confirm Statements	324729	TDI - ALL RH - JAN 3- Coming Soon Start and End Date	TDI	EFS, EFD, OKD	1	Confluence
6	01/01/2022			Date change	324729	TDI - ALL RH - JAN 1 - Coming Soon Start and End Date	TDI	EFS, EFD, OKD	1	Confluence
7	01/01/2022			Update system controls	334652	TDI - Den/JAX/GNF - JAN 1 and 6 - Tax Letter 0791 On/Off and Last Print Date	TDI	GNF, DEN, JAX	1	Confluence
8	12/20/2021			Missing CQ	324527	TDI - Dec 20 - Missing CQ records query - Prep - December	TDI	EFS, EFD, NHS, OKD	2	Confluence
9	12/20/2021			Stakeholder Review	293309	CBS - GNF - READ - BMP Nightly Control GR (SLHLXFK)	SLHLXFK	GNF	5	Test Results
10	12/17/2021			Pull xmls and send to CCM	324781	TDI - NELNET - Statements with 'Tax Ready' and tax data - December Prep	TDI	G19, Q12, Q13	3	xmls
11	12/17/2021			Self-Assessment	--	--	TDI	--	--
12	12/16/2021			Order NN Tax Ready Stmts	324781	TDI - NELNET - Statements with 'Tax Ready' and tax data - December Prep	TDI	G19, Q12, Q13	3
13	12/16/2021			Test Validation	292796	CBS - GNF - Letter Print Execute user program (SLEEUPK)	SLEEUPK	GNF	3	Test Results
14	12/14/2021			Test Validation	334291	TDI - GNF - Statements - Consider Special Stm Req Flag when updating Notification Media	SLA9UPR	GNF	5	Test Results
15	12/13/2021			PO Review	292796	PO Review for GNF Letter Print Execute (SLEEUPK)	SLEEUPK	GNF	--	Test Results
16	12/13/2021			Stakeholder Review	292796	Stakeholder Review for GNF Letter Print Execute (SLEEUPK)	SLEEUPK	GNF	--	Test Results
17	11/19/2021			Test Validation	331788	CBS - SLS - Skip - Skip Waterfall: Populate Vendor Input Candidates (SLSWVCP)	SLSWVCP	SLS	5	Test Results
18	11/17/2021			Write Test Steps	305979	CBS - SLS - UPDATE - MTE Process Borrowers (SLG5XEK)	SLG5XEK	SLS	5	Test Results
19	10/29/2021			Test Validation	292795	CBS - GNF - READ and Comaker - STM2 Create Statement Master Tables (SLT2UPR)	SLT2UPR	GNF	8	Test Results
20	10/27/2021			Test Validation	325932	TDI - EFS - (e-mail) 0791 Letter - new menu and screen (519038 LA)	SLADOC	EFS	5	Test Results
21	10/27/2021			Test Validation	326095	TDI - EFS- (e-mail) 0791 Letter - New program to identify population (519038 LA)	SLACIR	EFS	5	Test Results
22	10/14/2021			Write Test Steps	306229	CBS - SLS - C/C Crt SuppCallWrkRcds (SLLZXEK)	SLLZXEK	SLS	8	Test Results
23	10/14/2021			Write Test Steps	306266	CBS - SLS - Skip Trace Tags-903/904_2 (NBUPXEK) Custom	NBUPXEK	SLS	5	Test Results
24	10/14/2021			Write Test Steps	306040	CBS - SLS- ACD - LET Move Zip Codes (SLSWUSK)	SLSWUSK	SLS	5	Test Results
25	09/30/2021			Write Test Steps	305973	CBS - SLS - UPDATE - MTE Repair Borrower Rcd (SLF0E3K) Screen	SLF0E3K	SLS	5	Test Results
26	09/15/2021			Test Validation	310909	CBS - SLS - C/C Caller Contact Cntrl (SLHRXFK)	SLHRXFK	SLS	5	Test Results
27	09/15/2021			Test Validation	306032	CBS - SLS - BMP Nightly Control GR (SLHLXFK)	SLHLXFK	SLS	5	Test Results
28	09/14/2021			Test Validation	305971	CBS - SLS - UPDATE - EDIT BORROWER, PAGE 1 (SLDAE3K)	SLDAE3K	SLS	3	Test Results
29	08/31/2021			Test Validation	306254	CBS - SLS - Skip Trace-Driver (INQHXEK) Custom	INQHXEK	SLS	5	Test Results
30	08/31/2021			Write Test Steps	306256	CBS - SLS - Skip Trace-RPT CRT/File (INTVXFK) Custom	INTVXFK	SLS	5	Test Results
31	08/06/2021			Test Validation	306225	CBS - SLS - DSC Batch Disclosure (SLIKXDK)	SLIKXDK	SLS	5	Test Results
32	08/05/2021			Test Validation	312700	CBS - SLS - DSC Print Disclosure (SLHYUPK) CUSTOM	SLHYUPK	SLS	5	Test Results
33	07/21/2021			Write Test Steps	306248	CBS - SLS - UPDATE - API Process Borrowers (SLUJXEK)	SLUJXEK	SLS	8	Test Results
34	06/17/2021			Write Test Steps	306041	CBS - SLS - STM2 Create Statement Master Tables (SLT2UPR)	SLT2UPR	SLS	5	Test Results
35	06/16/2021			Test Validation	306033	CBS - SLS - DSC Print Disclosure (SLHYUPK) Core	SLHYUPK	SLS	5	Test Results
36	05/21/2021			Tested by Sokastis	306038	CBS - SLS - BMP C/C AutoDial (SLK3USK)	SLK3USK	SLS	5	Test Results
DONE

Cell Formulas
Range	Formula
L1	L1	=TODAY()

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Cell	does not contain an error	text	NO
A:A	Cell	does not contain a blank value	text	NO

 

[ebcopystop]

Here's a mini-sheet of the "DONE" sheet

Skedge.xlsm
	A			D	E	F	G	H	I	J	K	L
1	DONE Due Dt			Task	PBI #	PBI	Program	Client	Effort	References	Status	01/06/2022
2	01/04/2022			Test	340982	TDI - NDS - IAP - remove OID on PIF loans	SLH3CPP	NDS	5
3	01/04/2022			Test	340832	TDI - GNF - IAP - remove OID on PIF loans	SLH3CPP	GNF	5
4	01/03/2022			Missing CQ Overlapping	324529	TDI - Jan 3 - Overlapping transactions query - Prep - January	TDI	EFS, EFD, NHS, OKD	2
5	01/03/2022			Confirm Statements	324729	TDI - ALL RH - JAN 3- Coming Soon Start and End Date	TDI	EFS, EFD, OKD	1	Confluence
6	01/01/2022			Date change	324729	TDI - ALL RH - JAN 1 - Coming Soon Start and End Date	TDI	EFS, EFD, OKD	1	Confluence
7	01/01/2022			Update system controls	334652	TDI - Den/JAX/GNF - JAN 1 and 6 - Tax Letter 0791 On/Off and Last Print Date	TDI	GNF, DEN, JAX	1	Confluence
8	12/20/2021			Missing CQ	324527	TDI - Dec 20 - Missing CQ records query - Prep - December	TDI	EFS, EFD, NHS, OKD	2	Confluence
9	12/20/2021			Stakeholder Review	293309	CBS - GNF - READ - BMP Nightly Control GR (SLHLXFK)	SLHLXFK	GNF	5	Test Results
10	12/17/2021			Pull xmls and send to CCM	324781	TDI - NELNET - Statements with 'Tax Ready' and tax data - December Prep	TDI	G19, Q12, Q13	3	xmls
11	12/17/2021			Self-Assessment	--	--	TDI	--	--
12	12/16/2021			Order NN Tax Ready Stmts	324781	TDI - NELNET - Statements with 'Tax Ready' and tax data - December Prep	TDI	G19, Q12, Q13	3
13	12/16/2021			Test Validation	292796	CBS - GNF - Letter Print Execute user program (SLEEUPK)	SLEEUPK	GNF	3	Test Results
14	12/14/2021			Test Validation	334291	TDI - GNF - Statements - Consider Special Stm Req Flag when updating Notification Media	SLA9UPR	GNF	5	Test Results
15	12/13/2021			PO Review	292796	PO Review for GNF Letter Print Execute (SLEEUPK)	SLEEUPK	GNF	--	Test Results
16	12/13/2021			Stakeholder Review	292796	Stakeholder Review for GNF Letter Print Execute (SLEEUPK)	SLEEUPK	GNF	--	Test Results
17	11/19/2021			Test Validation	331788	CBS - SLS - Skip - Skip Waterfall: Populate Vendor Input Candidates (SLSWVCP)	SLSWVCP	SLS	5	Test Results
18	11/17/2021			Write Test Steps	305979	CBS - SLS - UPDATE - MTE Process Borrowers (SLG5XEK)	SLG5XEK	SLS	5	Test Results
19	10/29/2021			Test Validation	292795	CBS - GNF - READ and Comaker - STM2 Create Statement Master Tables (SLT2UPR)	SLT2UPR	GNF	8	Test Results
20	10/27/2021			Test Validation	325932	TDI - EFS - (e-mail) 0791 Letter - new menu and screen (519038 LA)	SLADOC	EFS	5	Test Results
21	10/27/2021			Test Validation	326095	TDI - EFS- (e-mail) 0791 Letter - New program to identify population (519038 LA)	SLACIR	EFS	5	Test Results
22	10/14/2021			Write Test Steps	306229	CBS - SLS - C/C Crt SuppCallWrkRcds (SLLZXEK)	SLLZXEK	SLS	8	Test Results
23	10/14/2021			Write Test Steps	306266	CBS - SLS - Skip Trace Tags-903/904_2 (NBUPXEK) Custom	NBUPXEK	SLS	5	Test Results
24	10/14/2021			Write Test Steps	306040	CBS - SLS- ACD - LET Move Zip Codes (SLSWUSK)	SLSWUSK	SLS	5	Test Results
25	09/30/2021			Write Test Steps	305973	CBS - SLS - UPDATE - MTE Repair Borrower Rcd (SLF0E3K) Screen	SLF0E3K	SLS	5	Test Results
26	09/15/2021			Test Validation	310909	CBS - SLS - C/C Caller Contact Cntrl (SLHRXFK)	SLHRXFK	SLS	5	Test Results
27	09/15/2021			Test Validation	306032	CBS - SLS - BMP Nightly Control GR (SLHLXFK)	SLHLXFK	SLS	5	Test Results
28	09/14/2021			Test Validation	305971	CBS - SLS - UPDATE - EDIT BORROWER, PAGE 1 (SLDAE3K)	SLDAE3K	SLS	3	Test Results
29	08/31/2021			Test Validation	306254	CBS - SLS - Skip Trace-Driver (INQHXEK) Custom	INQHXEK	SLS	5	Test Results
30	08/31/2021			Write Test Steps	306256	CBS - SLS - Skip Trace-RPT CRT/File (INTVXFK) Custom	INTVXFK	SLS	5	Test Results
31	08/06/2021			Test Validation	306225	CBS - SLS - DSC Batch Disclosure (SLIKXDK)	SLIKXDK	SLS	5	Test Results
32	08/05/2021			Test Validation	312700	CBS - SLS - DSC Print Disclosure (SLHYUPK) CUSTOM	SLHYUPK	SLS	5	Test Results
33	07/21/2021			Write Test Steps	306248	CBS - SLS - UPDATE - API Process Borrowers (SLUJXEK)	SLUJXEK	SLS	8	Test Results
34	06/17/2021			Write Test Steps	306041	CBS - SLS - STM2 Create Statement Master Tables (SLT2UPR)	SLT2UPR	SLS	5	Test Results
35	06/16/2021			Test Validation	306033	CBS - SLS - DSC Print Disclosure (SLHYUPK) Core	SLHYUPK	SLS	5	Test Results
36	05/21/2021			Tested by Sokastis	306038	CBS - SLS - BMP C/C AutoDial (SLK3USK)	SLK3USK	SLS	5	Test Results
DONE

Cell Formulas
Range	Formula
L1	L1	=TODAY()

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Cell	does not contain an error	text	NO
A:A	Cell	does not contain a blank value	text	NO

And here's where I'm sitting with your code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Date
If Not Intersect(Target, Range("K:K")) Is Nothing Then
If Target.Value = "Done" Then
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
LR = Sheets("DONE").Range("A" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy Destination:=Sheets("DONE").Range("A" & LR)
Target.EntireRow.Delete shift:=xlUp
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End If
End If
With Worksheets("DONE")
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("A2"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With Worksheets("DONE").Sort
.SetRange Range("A2:K" & Cells(Rows.Count, "A").End(xlUp).Row)
.Header = xlYes
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
End Sub

 

[ebcopystop]

Nice! You definitely fixed the insert issue, I'm stoked about that.

The sorting doesn't seem to be triggering

Spoke too soon....

 

[ebcopystop]

GREAT SCOTT! I think I've got it!!

While it doesn't allow me to insert a blank row, this modification will auto-sort the "TO DO" sheet rows by date. So instead of inserting a row, when I type a date in the last row, it will trigger the sort function. I am able to cut/insert a row as long as I have a date in Column A and I paste it within the same date range. Which totally works for what I'm aiming to do.

It will also auto-sort all rows on the "DONE" sheet upon the movement of a row from "TO DO" to "DONE".

THANK YOU for guiding me in the right direction! I wouldn't have made it this far without your help.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 2/21/2020 9:01:00 AM EST
If Target.Column = 11 Then
       If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
       Dim Lastrow As Long
       Dim SheetName As String
        SheetName = "DONE"
       Dim SearchValue As String
       SearchValue = "Done"
       If Target.Value = SearchValue Then
                 Lastrow = Sheets(SheetName).Cells(Rows.Count, 5).End(xlUp).Row + 1
                 Rows(Target.Row).Copy Sheets(SheetName).Rows(Lastrow)
                 Target.EntireRow.Delete                                                                 
      End If
End If
With Worksheets("TO DO")
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("A3"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Worksheets("TO DO").Sort
        .SetRange Range("A3:K500" & Cells(Rows.Count, "A").End(xlUp).Row)
        .Header = xlNo
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With