Forum Discussion
Auto move row to different sheet after entering completion date
SnowMan55 This is a great macro, which works well for me, with one exception - I do not want to delete the row in the Current sheet, only clear out the data that was in it. So, essentially just move the data in that row to the Completed sheet. How would I adjust that macro? Thanks!
Me.Range("A" & in4RowNum).EntireRow.clearcontents
https://learn.microsoft.com/en-us/office/vba/api/Excel.Range.ClearContents
- Is there a way to have the row that is moved to the "Completed" sheet go to the top of the of list rather than to the bottom of the list?
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 RstThanks 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.
