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

ebcopystop

New Member
Joined
Jan 5, 2022
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Does this do it...
VBA Code:
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
 
Upvote 0
Does this do it...
VBA Code:
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
 
Upvote 0
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
ABCDEFGHIJKL
1TO DO Due DtDayTimeTaskPBI #PBIProgramClientEffortReferencesStatus01/06/2022
2ON HOLDON HOLDTest293289CBS - GNF - CCA and READ - 3 BMP C/C RDP program modificationsSLIVXFKGNF8On hold
301/05/2022Wednesday9:00 AMTDI Extract Processing324716TDI - GSMR - JAN 5 - TDI extract Nelnet - PRODTDINHS, NHD2ConfluenceIn progress
401/05/2022WednesdayTest340183CBS - SLS - BUG - remove CBS Comaker - Letter PrintSLEEUPKSLS8In progress
501/06/2022Thursday9:00 AMTDI Extract Processing324719TDI - EFS and EFD - JAN 6 - TDI extract Nelnet - PRODTDIEFS, EFD3Done
601/06/2022Thursday11:00 AMLog in324826TDI - ALL - SPIKE - Update IRS Filing Schedule - January 4TDI--1ConfluenceTo Do
701/06/2022Thursday9:00 AMRun email marking (Update)/MFT release330129TDI - EFS and EFD - JAN 3 and 6 - Email marking - PROD - BEFORE Tax ExtractTDIEFS, EFD2To Do
801/06/2022Thursday10:00 AMDate change334652TDI - Den/JAX/GNF - JAN 1 and 6 - Tax Letter 0791 On/Off and Last Print DateTDIGNF, DEN, JAX1ConfluenceTo Do
901/06/2022Thursday9:00 AMUpdate Tax Ready text PROD324773TDI - EFD - Jan 6 - Statements with 'Tax Ready' - January ProdTDIEFD2To Do
1001/06/2022Thursday9:00 AMTDI Extract Processing324713TDI - OSLA - JAN 6 - TDI extract Nelnet - PRODTDIOKD3To Do
1101/06/2022Thursday9:00 AMUpdate Tax Ready text PROD324777TDI - EFS - Jan 6 - Statements with 'Tax Ready' - January ProdTDIEFS2To Do
1201/06/2022Thursday9:00 AMUpdate Tax Ready text PROD324780TDI - OKD - Jan 6 - Statements with 'Tax Ready' - January ProdTDIOKD2To Do
1301/06/2022Thursday9:00 AMGenerate tax letters 0791324784TDI - NHS - PROD - Generate Tax Letter (0791) - January 6TDINHS3To Do
1401/06/2022Thursday9:00 AMGenerate tax letters 0791324786TDI - NHD - PROD - Generate Tax Letter (0791) - January 6TDINHD3To Do
1501/06/2022Thursday9:00 AMTDI Extract Processing324704TDI - NN - JAN 6 - TDI extract Nelnet - PRODTDIGNF, DEN, JAX3To Do
TO DO
Cell Formulas
RangeFormula
L1L1=TODAY()
B2:B15B2=TEXT(A2,"dddd")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1,A2:B1048576Dates OccurringyesterdaytextNO
A1,A2:B1048576Dates OccurringtodaytextNO
A1,A2:B1048576Dates OccurringtomorrowtextNO
A1,A2:B1048576Dates Occurringthis weektextNO
 
Upvote 0
Here's a mini-sheet of the "DONE" sheet

Skedge.xlsm
ADEFGHIJKL
1DONE Due DtTaskPBI #PBIProgramClientEffortReferencesStatus01/06/2022
201/04/2022Test340982TDI - NDS - IAP - remove OID on PIF loansSLH3CPPNDS5
301/04/2022Test340832TDI - GNF - IAP - remove OID on PIF loansSLH3CPPGNF5
401/03/2022Missing CQ Overlapping324529TDI - Jan 3 - Overlapping transactions query - Prep - JanuaryTDIEFS, EFD, NHS, OKD2
501/03/2022Confirm Statements324729TDI - ALL RH - JAN 3- Coming Soon Start and End DateTDIEFS, EFD, OKD1Confluence
601/01/2022Date change324729TDI - ALL RH - JAN 1 - Coming Soon Start and End DateTDIEFS, EFD, OKD1Confluence
701/01/2022Update system controls334652TDI - Den/JAX/GNF - JAN 1 and 6 - Tax Letter 0791 On/Off and Last Print DateTDIGNF, DEN, JAX1Confluence
812/20/2021Missing CQ324527TDI - Dec 20 - Missing CQ records query - Prep - DecemberTDIEFS, EFD, NHS, OKD2Confluence
912/20/2021Stakeholder Review293309CBS - GNF - READ - BMP Nightly Control GR (SLHLXFK)SLHLXFKGNF5Test Results
1012/17/2021Pull xmls and send to CCM324781TDI - NELNET - Statements with 'Tax Ready' and tax data - December PrepTDIG19, Q12, Q133xmls
1112/17/2021Self-Assessment----TDI----
1212/16/2021Order NN Tax Ready Stmts324781TDI - NELNET - Statements with 'Tax Ready' and tax data - December PrepTDIG19, Q12, Q133
1312/16/2021Test Validation292796CBS - GNF - Letter Print Execute user program (SLEEUPK)SLEEUPKGNF3Test Results
1412/14/2021Test Validation334291TDI - GNF - Statements - Consider Special Stm Req Flag when updating Notification MediaSLA9UPRGNF5Test Results
1512/13/2021PO Review292796PO Review for GNF Letter Print Execute (SLEEUPK)SLEEUPKGNF--Test Results
1612/13/2021Stakeholder Review292796Stakeholder Review for GNF Letter Print Execute (SLEEUPK)SLEEUPKGNF--Test Results
1711/19/2021Test Validation331788CBS - SLS - Skip - Skip Waterfall: Populate Vendor Input Candidates (SLSWVCP)SLSWVCPSLS5Test Results
1811/17/2021Write Test Steps305979CBS - SLS - UPDATE - MTE Process Borrowers (SLG5XEK)SLG5XEKSLS5Test Results
1910/29/2021Test Validation292795CBS - GNF - READ and Comaker - STM2 Create Statement Master Tables (SLT2UPR)SLT2UPRGNF8Test Results
2010/27/2021Test Validation325932TDI - EFS - (e-mail) 0791 Letter - new menu and screen (519038 LA)SLADOCEFS5Test Results
2110/27/2021Test Validation326095TDI - EFS- (e-mail) 0791 Letter - New program to identify population (519038 LA)SLACIREFS5Test Results
2210/14/2021Write Test Steps306229CBS - SLS - C/C Crt SuppCallWrkRcds (SLLZXEK)SLLZXEKSLS8Test Results
2310/14/2021Write Test Steps306266CBS - SLS - Skip Trace Tags-903/904_2 (NBUPXEK) CustomNBUPXEKSLS5Test Results
2410/14/2021Write Test Steps306040CBS - SLS- ACD - LET Move Zip Codes (SLSWUSK)SLSWUSKSLS5Test Results
2509/30/2021Write Test Steps305973CBS - SLS - UPDATE - MTE Repair Borrower Rcd (SLF0E3K) ScreenSLF0E3KSLS5Test Results
2609/15/2021Test Validation310909CBS - SLS - C/C Caller Contact Cntrl (SLHRXFK)SLHRXFKSLS5Test Results
2709/15/2021Test Validation306032CBS - SLS - BMP Nightly Control GR (SLHLXFK)SLHLXFKSLS5Test Results
2809/14/2021Test Validation305971CBS - SLS - UPDATE - EDIT BORROWER, PAGE 1 (SLDAE3K)SLDAE3KSLS3Test Results
2908/31/2021Test Validation306254CBS - SLS - Skip Trace-Driver (INQHXEK) CustomINQHXEKSLS5Test Results
3008/31/2021Write Test Steps306256CBS - SLS - Skip Trace-RPT CRT/File (INTVXFK) CustomINTVXFKSLS5Test Results
3108/06/2021Test Validation306225CBS - SLS - DSC Batch Disclosure (SLIKXDK)SLIKXDKSLS5Test Results
3208/05/2021Test Validation312700CBS - SLS - DSC Print Disclosure (SLHYUPK) CUSTOMSLHYUPKSLS5Test Results
3307/21/2021Write Test Steps306248CBS - SLS - UPDATE - API Process Borrowers (SLUJXEK)SLUJXEKSLS8Test Results
3406/17/2021Write Test Steps306041CBS - SLS - STM2 Create Statement Master Tables (SLT2UPR)SLT2UPRSLS5Test Results
3506/16/2021Test Validation306033CBS - SLS - DSC Print Disclosure (SLHYUPK) CoreSLHYUPKSLS5Test Results
3605/21/2021Tested by Sokastis306038CBS - SLS - BMP C/C AutoDial (SLK3USK)SLK3USKSLS5Test Results
DONE
Cell Formulas
RangeFormula
L1L1=TODAY()
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A:ACelldoes not contain an errortextNO
A:ACelldoes not contain a blank value textNO
 
Upvote 0
Here's a mini-sheet of the "DONE" sheet

Skedge.xlsm
ADEFGHIJKL
1DONE Due DtTaskPBI #PBIProgramClientEffortReferencesStatus01/06/2022
201/04/2022Test340982TDI - NDS - IAP - remove OID on PIF loansSLH3CPPNDS5
301/04/2022Test340832TDI - GNF - IAP - remove OID on PIF loansSLH3CPPGNF5
401/03/2022Missing CQ Overlapping324529TDI - Jan 3 - Overlapping transactions query - Prep - JanuaryTDIEFS, EFD, NHS, OKD2
501/03/2022Confirm Statements324729TDI - ALL RH - JAN 3- Coming Soon Start and End DateTDIEFS, EFD, OKD1Confluence
601/01/2022Date change324729TDI - ALL RH - JAN 1 - Coming Soon Start and End DateTDIEFS, EFD, OKD1Confluence
701/01/2022Update system controls334652TDI - Den/JAX/GNF - JAN 1 and 6 - Tax Letter 0791 On/Off and Last Print DateTDIGNF, DEN, JAX1Confluence
812/20/2021Missing CQ324527TDI - Dec 20 - Missing CQ records query - Prep - DecemberTDIEFS, EFD, NHS, OKD2Confluence
912/20/2021Stakeholder Review293309CBS - GNF - READ - BMP Nightly Control GR (SLHLXFK)SLHLXFKGNF5Test Results
1012/17/2021Pull xmls and send to CCM324781TDI - NELNET - Statements with 'Tax Ready' and tax data - December PrepTDIG19, Q12, Q133xmls
1112/17/2021Self-Assessment----TDI----
1212/16/2021Order NN Tax Ready Stmts324781TDI - NELNET - Statements with 'Tax Ready' and tax data - December PrepTDIG19, Q12, Q133
1312/16/2021Test Validation292796CBS - GNF - Letter Print Execute user program (SLEEUPK)SLEEUPKGNF3Test Results
1412/14/2021Test Validation334291TDI - GNF - Statements - Consider Special Stm Req Flag when updating Notification MediaSLA9UPRGNF5Test Results
1512/13/2021PO Review292796PO Review for GNF Letter Print Execute (SLEEUPK)SLEEUPKGNF--Test Results
1612/13/2021Stakeholder Review292796Stakeholder Review for GNF Letter Print Execute (SLEEUPK)SLEEUPKGNF--Test Results
1711/19/2021Test Validation331788CBS - SLS - Skip - Skip Waterfall: Populate Vendor Input Candidates (SLSWVCP)SLSWVCPSLS5Test Results
1811/17/2021Write Test Steps305979CBS - SLS - UPDATE - MTE Process Borrowers (SLG5XEK)SLG5XEKSLS5Test Results
1910/29/2021Test Validation292795CBS - GNF - READ and Comaker - STM2 Create Statement Master Tables (SLT2UPR)SLT2UPRGNF8Test Results
2010/27/2021Test Validation325932TDI - EFS - (e-mail) 0791 Letter - new menu and screen (519038 LA)SLADOCEFS5Test Results
2110/27/2021Test Validation326095TDI - EFS- (e-mail) 0791 Letter - New program to identify population (519038 LA)SLACIREFS5Test Results
2210/14/2021Write Test Steps306229CBS - SLS - C/C Crt SuppCallWrkRcds (SLLZXEK)SLLZXEKSLS8Test Results
2310/14/2021Write Test Steps306266CBS - SLS - Skip Trace Tags-903/904_2 (NBUPXEK) CustomNBUPXEKSLS5Test Results
2410/14/2021Write Test Steps306040CBS - SLS- ACD - LET Move Zip Codes (SLSWUSK)SLSWUSKSLS5Test Results
2509/30/2021Write Test Steps305973CBS - SLS - UPDATE - MTE Repair Borrower Rcd (SLF0E3K) ScreenSLF0E3KSLS5Test Results
2609/15/2021Test Validation310909CBS - SLS - C/C Caller Contact Cntrl (SLHRXFK)SLHRXFKSLS5Test Results
2709/15/2021Test Validation306032CBS - SLS - BMP Nightly Control GR (SLHLXFK)SLHLXFKSLS5Test Results
2809/14/2021Test Validation305971CBS - SLS - UPDATE - EDIT BORROWER, PAGE 1 (SLDAE3K)SLDAE3KSLS3Test Results
2908/31/2021Test Validation306254CBS - SLS - Skip Trace-Driver (INQHXEK) CustomINQHXEKSLS5Test Results
3008/31/2021Write Test Steps306256CBS - SLS - Skip Trace-RPT CRT/File (INTVXFK) CustomINTVXFKSLS5Test Results
3108/06/2021Test Validation306225CBS - SLS - DSC Batch Disclosure (SLIKXDK)SLIKXDKSLS5Test Results
3208/05/2021Test Validation312700CBS - SLS - DSC Print Disclosure (SLHYUPK) CUSTOMSLHYUPKSLS5Test Results
3307/21/2021Write Test Steps306248CBS - SLS - UPDATE - API Process Borrowers (SLUJXEK)SLUJXEKSLS8Test Results
3406/17/2021Write Test Steps306041CBS - SLS - STM2 Create Statement Master Tables (SLT2UPR)SLT2UPRSLS5Test Results
3506/16/2021Test Validation306033CBS - SLS - DSC Print Disclosure (SLHYUPK) CoreSLHYUPKSLS5Test Results
3605/21/2021Tested by Sokastis306038CBS - SLS - BMP C/C AutoDial (SLK3USK)SLK3USKSLS5Test Results
DONE
Cell Formulas
RangeFormula
L1L1=TODAY()
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A:ACelldoes not contain an errortextNO
A:ACelldoes not contain a blank value textNO

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
 
Upvote 0
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.

VBA Code:
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
 
Upvote 0
Solution

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top