Forum Discussion
Auto move row to different sheet after entering completion date
If you keep all datas in Sheet1,you dynamic filter both Completed and not Complete with sql like below:
set Conn = CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties='Excel 12.0;';Data Source=" & ThisWorkbook.FullName
sql="select * from [Sheet1$]"
fields="*"
criteria=" where instr(""Completed"",Status)>0"
sql="select " & fields & " from [Sheet1$]" & criteria & " order by rowid desc"
'you should have a column rowid in Sheet1 for order the records desc
Set Rst=CreateObject("ADODB.Recordset")
Rst.CursorLocation = 3
Rst.open sql,conn,1,3
ActiveCell.CopyFromRecordset Rst
Thanks peiyezhu! I have a separate sheet titled "Completed," so moving them from Sheet 1 to Completed. I would like the most recent completed rows to go to the top of the "Completed" sheet so it is oldest on the bottom and newest at the top.
Do we add the SQL underneath our code in our Module?
I have attached a snippet of the codes that I currently have.
- would like the most recent completed rows to go to the top of the "Completed" sheet
This code about ADO is mean to extract all complete records from master sheet sheet1 rather than most recent completed rows because you want the new complete rows should at top of the list.