Paste Values Instead of Formulas

[Legacy 436357]

Hello I have this code derived in this forum but it pastes the rows with formulas. Can it be altered so it pastes the values of the cells?

Thank you for your help

Sub MyMacro()


    Dim lr As Long
    Dim r As Long
    Dim nr As Long
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column A on Sheet1
    lr = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows starting on row 2 on Sheet1
    For r = 2 To lr
        If Sheets("Sheet1").Cells(r, "L") = True Then
'           Find next available row on Sheet2
            nr = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
'           Copy data to from columns A-L to sheet2
            Sheets("Sheet1").Range(Cells(r, "A"), Cells(r, "L")).Copy Sheets("Sheet2").Cells(nr, "A")
'           Remove checkbox from sheet 1
            Sheets("Sheet1").Cells(r, "L") = False
        End If
    Next r
    
    Application.ScreenUpdating = True
    
    MsgBox "Macro Done!"
    
End Sub

 

[Norie]

Try this.

Sub MyMacro()


    Dim lr As Long
    Dim r As Long
    Dim nr As Long
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column A on Sheet1
    lr = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows starting on row 2 on Sheet1
    For r = 2 To lr
        If Sheets("Sheet1").Cells(r, "L") = True Then
'           Find next available row on Sheet2
            nr = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
'           Copy data to from columns A-L to sheet2
            Sheets("Sheet1").Range("A" & r).Resize(,12).Copy 
            Sheets("Sheet2").Cells(nr, "A").PasteSpecial xlPasteValues
'           Remove checkbox from sheet 1
            Sheets("Sheet1").Cells(r, "L") = False
        End If
    Next r
    
    Application.ScreenUpdating = True
    
    MsgBox "Macro Done!"
    
End Sub

 

[Legacy 436357]

Thank you that works but one thing though. The last row is flashing from the source sheet like it's ready to be copied when running the code.

 

[Norie]

Add this at the end of the code.

Application.CutCopyMode = False

 

[Legacy 436357]

Perfect thank you very much for your help.

I am learning valuable lessons here hopefully I can pay it forward.