Excel VBA Moving Row Values [Only] from one Sheet to another - Current code is working, just needs t-CodePudding

This is currently the setup that I have found helpful and have modified to work well... However, I'm struggling with one small further and final modification. I would like to just - Paste Values as opposed to the Formulas.

Sub move_rows_to_another_sheet()
    '
    Sheets("User").Select
    Columns("A:Y").Select
    Range("A:Y").Activate
    '
    For Each myCell In Selection.Columns(25).Cells
        If myCell.Value = "Closed" Then
            myCell.EntireRow.Copy Worksheets("Archive").Range("A" & Rows.Count).End(3)(2)
            myCell.EntireRow.Delete
        End If
    Next
    '
    Range("A2").Select
End Sub

''Updated Version - Move Single Rows
'
Sub move_rows_to_another_sheet()
    '
    Sheets("Users").Select
    Columns("A:Y").Select
    Range("A:Y").Activate
    '
    For Each mycell In Selection.Columns(25).Cells
    '
    If mycell.Value = "Closed" Then
        mycell.EntireRow.Copy
        Worksheets("Archive").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        mycell.EntireRow.Delete
    End If
    Next
    '
Range("A2").Select
End Sub

The idea is simple... The current code is successful, however, I would like to just copy and paste the [values] of the rows cell content and [not] the formulas etc. The formatting is fine and everything, I just need the result of the functioning formulas recorded.

I have tried various options such as [myCell.EntireRow.CopyValues] even [& Rows.Count & Rows.PasteSpecial]... Any thoughts?

Thanks in advance

CodePudding user response：

I tried your code. It looks like when there are several cells with "closed" it will not work for all. because when one deletes, one should delete from below upwards. But then the data in Archive is not in right order.

In your original code you can make the range smaller, so it will run faster.

or take what you want from this code:

 Sub move_rows_to_another_sheet2()

    
    Dim mycell As Range
    Dim checkClosed
    Dim Lastrow As Long
    Dim i As Long
    
    
    Set checkClosed = ThisWorkbook.Worksheets("User").Range("Y1:Y10000")
    Lastrow = Worksheets("Archive").Cells(Rows.Count, 1).End(xlUp).Row   1 'one cell below last used cell in column A

    For i = 10000 To 1 Step -1 'from row 10000 to row 1
        Set mycell = ThisWorkbook.Worksheets("User").Cells(i, "Y")
        
        If LCase(mycell.Value) = "closed" Then 'checks for Closed and closed
            mycell.EntireRow.Copy
            Worksheets("Archive").Range("A" & Lastrow).PasteSpecial Paste:=xlPasteValues
            mycell.EntireRow.Delete
            Lastrow = Lastrow   1
        End If
    Next i
    '
    Range("A2").Select
End Sub