Paste Values in VBA

[Rob_010101]

Hi All,

The following code moves data to an archive sheet, based on the value selected from a list in column P.

It also looks to see if filters have been applied and ensures the data is moved to the next available row, regardless of filtered data.

It works perfectly, however, I'd like the archived row to be values only and not the formulas. Can this be done easily?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim fromRow%, archiveRow%, archiveList As Worksheet

    If Target.Cells.Count > 1 Then Exit Sub
 
    If Not Application.Intersect(Target, Range("P2:P500000")) Is Nothing Then 'amend this range address to your
        Set archiveList = ThisWorkbook.Worksheets("Archive")
            If Target.Value = "Archive" Then
                fromRow = ActiveCell.Row
              
                With archiveList
                    If .FilterMode Then
                        Dim strMatch As String
                        strMatch = "match" & Replace("(2,1/(a:a>""""),1)", "a:a", .AutoFilter.Range.Cells(1).EntireColumn.Address(0, 0, 1, 1))
                        archiveRow = Evaluate(strMatch) + 1
                    Else
                        archiveRow = archiveList.Cells(archiveList.Rows.Count, 1).End(3).Row + 1
                    End If
                End With
              
                Range(Cells(fromRow, 1), Cells(fromRow, 16)).Copy archiveList.Cells(archiveRow, 1)
                Rows(fromRow).EntireRow.Delete
            End If
    End If
End Sub

 

[mohadin]

Rplace

Range(Cells(fromRow, 1), Cells(fromRow, 16)).Copy archiveList.Cells(archiveRow, 1)

With

Range(Cells(fromRow, 1), Cells(fromRow, 16)).Copy
 archiveList.Cells(archiveRow, 1).PasteSpecial Paste:=xlPasteValues

 

[MARK858]

Try changing

Range(Cells(fromRow, 1), Cells(fromRow, 16)).Copy archiveList.Cells(archiveRow, 1)

to

Range(Cells(fromRow, 1), Cells(fromRow, 16)).Copy
archiveList.Cells(archiveRow, 1).PasteSpecial xlValues
Application.CutCopyMode = False

or

    With Range(Cells(fromRow, 1), Cells(fromRow, 16))
        archiveList.Cells(archiveRow, 1).Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With

 

[jasonb75]

Perhaps like this

                Range(Cells(fromRow, 1), Cells(fromRow, 16)).Copy
                archiveList.Cells(archiveRow, 1).PasteSpecial xlValues
                Application.CutCopyMode = False
                Rows(fromRow).EntireRow.Delete

 

[Rob_010101]

Rplace

Range(Cells(fromRow, 1), Cells(fromRow, 16)).Copy archiveList.Cells(archiveRow, 1)

With

Range(Cells(fromRow, 1), Cells(fromRow, 16)).Copy
 archiveList.Cells(archiveRow, 1).PasteSpecial Paste:=xlPasteValues

Thank you

 

[Rob_010101]

Try changing

Range(Cells(fromRow, 1), Cells(fromRow, 16)).Copy archiveList.Cells(archiveRow, 1)

to

Range(Cells(fromRow, 1), Cells(fromRow, 16)).Copy
archiveList.Cells(archiveRow, 1).PasteSpecial xlValues
Application.CutCopyMode = False

or

    With Range(Cells(fromRow, 1), Cells(fromRow, 16))
        archiveList.Cells(archiveRow, 1).Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With

Yes, this one worked the best.

Thank you very much

 

[Rob_010101]

Perhaps like this

                Range(Cells(fromRow, 1), Cells(fromRow, 16)).Copy
                archiveList.Cells(archiveRow, 1).PasteSpecial xlValues
                Application.CutCopyMode = False
                Rows(fromRow).EntireRow.Delete

Thank you

 

[MARK858]

You're welcome, happy we could help