Change VB code to move as values, instead of formulas

[Rob_010101]

Hello

The below code moves rows of data to an archive sheet when they become 6 months old (as of the current date).

Could someone help me change it so it moves the data as values, rather than formulas?

Private Sub Workbook_Open()

    Application.ScreenUpdating = False
    
    'Set worksheet variables
    Dim WsC6 As Worksheet, WsP6 As Worksheet
    Set WsC6 = Worksheets("PAYE Sickness (Current 6)")
    Set WsP6 = Worksheets("PAYE Sickness (Archive)")
    
    'Move old records from the PAYE Sickness (Current 6) sheet first
    Dim d As Date, d2 As Date, LRow As Long
    d = WorksheetFunction.EDate(Date, -6)
    d = Format(d, "dd/mm/yyyy")
    LRow = WsP6.Cells(Rows.Count, 1).End(3).Row + 1
    With WsC6.Range("A1").CurrentRegion
        .AutoFilter 10, "<" & CDbl(d)
        If WsC6.Cells(Rows.Count, 1).End(xlUp).Row > 1 Then
            .Offset(1).Resize(.Rows.Count - 1).Copy WsP6.Cells(LRow, 1)
            .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
        End If
        WsC6.ShowAllData
    End With
    
    Application.ScreenUpdating = True
        Sheets("PAYE Sickness (Current 6)").Select
  Range("I1:I" & Range("I" & Rows.Count).End(3).Row + 1).Find("", Range("I1"), xlValues, xlWhole).Select
End Sub

Many Thanks

 

[saboh12617]

Hello,
You can edit this part of the code like so

        If WsC6.Cells(Rows.Count, 1).End(xlUp).Row > 1 Then
            .Offset(1).Resize(.Rows.Count - 1).Copy
            WsP6.Cells(LRow, 1).PasteSpecial xlPasteValues
            Application.CutCopyMode = False
            .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
        End If

 

[Rob_010101]

Hello,
You can edit this part of the code like so

        If WsC6.Cells(Rows.Count, 1).End(xlUp).Row > 1 Then
            .Offset(1).Resize(.Rows.Count - 1).Copy
            WsP6.Cells(LRow, 1).PasteSpecial xlPasteValues
            Application.CutCopyMode = False
            .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
        End If

Thank you!

 

[saboh12617]

Glad to help, have a good day!