Copy row based on cell value and paste as text in another worksheet

[DH937]

I am wanting to cut an entire row and paste it into a new worksheet based on a cell value. I can currently do that but the problem I am having is when it goes into the new work sheet it is keeping the formulas, but I only want the formula result/text value. I have searched online and tried the PasteSpecial command within the current code I am using but cannot seem to get it to work.

Any help is greatly appreciated, thank you!



Sub MoveRowsToPendingReceipt()
Sheet1.Unprotect Password:="A6905"
Sheet2.Unprotect Password:="A6905"
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("Data Entry")
Set targetSheet = ThisWorkbook.Worksheets("Pending Receipt")

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

' Loop through each row in the source sheet
For i = 2 To lastRow
' Check if cell in column F contains "Complete"
If sourceSheet.Cells(i, "F").Value = "Complete" 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
Worksheets("Pending Receipt").Range("B2:E1000").Locked = True
Sheet1.Protect Password:="A6905"
Sheet2.Protect Password:="A6905"
End Sub

 

[RaulARodriguez]

Modify using a Destination Range

Sub MoveRowsToPendingReceipt()
Sheet1.Unprotect Password:="A6905"
Sheet2.Unprotect Password:="A6905"
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim Destrng As Range
Dim lastRow As Long
Dim i As Long

' Set the source and target sheets
Set sourceSheet = ThisWorkbook.Worksheets("Data Entry")
Set targetSheet = ThisWorkbook.Worksheets("Pending Receipt")

Set Destrng = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Offset(1)

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

' Loop through each row in the source sheet
For i = 2 To lastRow
' Check if cell in column F contains "Complete"
If sourceSheet.Cells(i, "F").Value = "Complete" Then
' Copy the entire row to the target sheet
sourceSheet.Rows(i).Copy 
'Destination:=targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Offset(1)
 Destrng.PasteSpecial xlPasteValues
' 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
Worksheets("Pending Receipt").Range("B2:E1000").Locked = True
Sheet1.Protect Password:="A6905"
Sheet2.Protect Password:="A6905"
End Sub

 

[DH937]

Worked perfectly, thank you!