Welcome to KD Jayakody blog. This effort is to provide you another tested code for excel macro. However, this code is powered by ChatGPT. I used chatgpt to generate this. However, had to waste more time to find this. cause, didn’t have any idea to ask it. By the way check the following statemet first.
following is the table structure with the data in the Tasks sheet. SDADSD Pending Hell Done bBello Pending sdadsad Pending The same table structure has been designed in the sheet named "Completed" too. What we need to do is when the status updated to the done. it should be removed from the Tasks sheet and move it to the completed sheet. can you write a macro for this.
As you see above i mentioned what i could tell him. Maybe grammar is not correct. because i just needed to paste the original text i entered to ChatGPT. However, following is the final response.
Sub MoveToCompleted()
Dim tblTasks As ListObject, tblCompleted As ListObject
Dim lastRow As Long, i As Long
Dim taskStatus As Range
'Get the tables from the worksheets
Set tblTasks = Sheets("Tasks").ListObjects("Tasks")
Set tblCompleted = Sheets("Completed").ListObjects("Completed")
'Loop through each row in the "Tasks" table
lastRow = tblTasks.Range.Rows.Count
For i = lastRow To 1 Step -1
Set taskStatus = tblTasks.ListColumns("Status").DataBodyRange(i)
If taskStatus.Value = "Done" Then
'Copy the task and status to the "Completed" table and delete from "Tasks" table
tblCompleted.ListRows.Add
tblCompleted.ListColumns("Task").DataBodyRange(tblCompleted.ListRows.Count).Value = tblTasks.ListColumns("Task").DataBodyRange(i).Value
tblCompleted.ListColumns("Status").DataBodyRange(tblCompleted.ListRows.Count).Value = tblTasks.ListColumns("Status").DataBodyRange(i).Value
tblTasks.ListRows(i).Delete
End If
Next i
End Sub
