cut/copy and paste row based on cell value not being equal to specific text

[mlan1604]

Hello
I'm moving completed tasks from one sheet to another and want to be able to move a task back into my worklist sheet.
I have a vba code in Excel for cutting and pasting rows from one sheet to another based on cell value="completed". Now I want to be able to reverse fromthe completed worksheet, by cutting and pasting rows not equal to the specific text string.
I thought, that I could just change "=" to "<>" in my code line [If sourceSheet.Cells(i, "C").Value = "Completed" Then], but then I get the error mesage, that my clipboard is used by another application. I have a list of choices (not startet; ongoing; delayed; completed), so I can't just pick another specific text string.

My code:
Sub MoveRowsToCompleted()
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim lastRow As Long
Dim i As Long

' Set the source and target sheets
Set sourceSheet = ThisWorkbook.Worksheets("Worklist")
Set targetSheet = ThisWorkbook.Worksheets("Completed")

' Find the last row in the source sheet
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "B").End(xlUp).Row

' Loop through each row in the source sheet
For i = 3 To lastRow
' Check if cell in column C contains "Completed"
If sourceSheet.Cells(i, "C").Value = "Completed" Then
' Copy the entire row to the target sheet
sourceSheet.Rows(i).Copy Destination:=targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Offset(1)
' Delete the row from the source sheet
sourceSheet.Rows(i).Delete
' Decrement the loop counter as the rows are shifting up
i = i - 1
' Update the last row value
lastRow = lastRow - 1
End If
Next i
End Sub

Sub MoveRowsToWorklist()
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim lastRow As Long
Dim i As Long

' Set the source and target sheets
Set sourceSheet = ThisWorkbook.Worksheets("Completed")
Set targetSheet = ThisWorkbook.Worksheets("Worklist")

' Find the last row in the source sheet
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "B").End(xlUp).Row

' Loop through each row in the source sheet
For i = 3 To lastRow
' Check if cell in column C contains "Completed"
If sourceSheet.Cells(i, "C").Value <> "Completed" Then
' Copy the entire row to the target sheet
sourceSheet.Rows(i).Copy Destination:=targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Offset(1)
' Delete the row from the source sheet
sourceSheet.Rows(i).Delete
' Decrement the loop counter as the rows are shifting up
i = i - 1
' Update the last row value
lastRow = lastRow - 1
End If
Next i
End Sub

Can someone help me solve the problem?

Thanks
MLAN1604

 

[DanteAmor]

I made new macros with another approach to move the records from one sheet to another:

Sub MoveRowsToCompleted_v1()
  Application.ScreenUpdating = False
  With Sheets("Worklist")
    .Range("A2", .Range("C" & Rows.Count).End(3)).AutoFilter 3, "Completed"
    .AutoFilter.Range.Offset(1).EntireRow.Copy Sheets("Completed").Range("A" & Rows.Count).End(3)(2)
    .AutoFilter.Range.Offset(1).EntireRow.Delete
    .ShowAllData
  End With
  Application.ScreenUpdating = True
End Sub

Sub MoveRowsToWorklist_v1()
  Application.ScreenUpdating = False
  With Sheets("Completed")
    .Range("A2", .Range("C" & Rows.Count).End(3)).AutoFilter 3, "<>Completed"
    .AutoFilter.Range.Offset(1).EntireRow.Copy Sheets("Worklist").Range("A" & Rows.Count).End(3)(2)
    .AutoFilter.Range.Offset(1).EntireRow.Delete
    .ShowAllData
  End With
  Application.ScreenUpdating = True
End Sub

 

[mlan1604]

Thank you for your help DanteAmor

I have tested your approach, but it still fails on
.Range("A2", .Range("C" & Rows.Count).End(3)).AutoFilter 3, "<>Completed"

 

[DanteAmor]

I have tested your approach, but it still fails on
.Range("A2", .Range("C" & Rows.Count).End(3)).AutoFilter 3, "<>Completed"

Does it send you any errors? What does the error say?
You can put an image of your "Completed" sheet, there are supposed to be values in column "C" other than "Completed".

 

[mlan1604]

Hi
Visual Basic gives me a Run-time error 1004: The method AutoFilter for the class Range dit not succed.
When I choose debug the above mentioned code line is highlighted.
I have attached a screenshot. It's in danish, but I have translated the names in my code (Fuldført=completed)

It works fine from Worklist->Completed but fails when going from Completed->Worklist

For me it seems to be the use of "<>" that causes the problem, but I don't know how else to exclude "Completed"

 

[DanteAmor]

I have no problems with "<>".
Maybe you have problems on that sheet. Rename that sheet.
Create a new sheet and name it "Completed", write some test data. In column C write data other than "Completed", try the macro again.

 

[mlan1604]

That did the trick. I will recreate my "completed" sheet.
To find out if it was my sheet all along, I also tested my old code on the new sheet, but it still came up with the same error, so I will be using your code.

Thank you for your help DanteAmor. I will mark your first responce as solved.