VBA Help!!! Paste Special Value based on Existing Code

[kenchristensen11]

Hello,

I have the below code but after it copies the entire row to the target sheet, the formulas are still there.

What I need to it do is Paste them as values (to get rid of the formula) after it's copied to the target sheet, BUT also keep formatting the same from the source sheet.

Sub MoveRowsToArchive()
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("Quarterly Prep")
Set targetSheet = ThisWorkbook.Worksheets("Archive")

' 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 = 9 To lastRow
' Check if cell in column X contains "Archive"
If sourceSheet.Cells(i, "B").Value = "Archive" 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

 

[6StringJazzer]

Replace the one line that currently does the copy/paste with these lines. Cannot test without your file.

sourceSheet.Rows(i).Copy
targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = xlCopy

 

[kenchristensen11]

Replace the one line that currently does the copy/paste with these lines. Cannot test without your file.

sourceSheet.Rows(i).Copy
targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = xlCopy

Thank you. Is there any way where instead of copying the ENTIRE row from the source sheet, it selects the data in the row from columns B to I only if a value in column B = "Archive" from the source sheet to the target sheet, starting in cell B5 in the target sheet?

 

[Armstrong_N14]

a

Thank you. Is there any way where instead of copying the ENTIRE row from the source sheet, it selects the data in the row from columns B to I only if a value in column B = "Archive" from the source sheet to the target sheet, starting in cell B5 in the target sheet?

Add a code that filters column B based on your criteria before the past as value code. I'd use a helper column filtering 1 or 0 probably on column A if its not occupied with any. Hope it helps.