Help with my code - I want to clear any formulas when pasting

[markpem]

Hello

I have some code which works really well:-

Option Explicit

Dim s1rw As Long, s2rw As Long, col As Long, endcol As Long
Cancel = True ' Ignore error messages as first column is LOCKED
Sheets("Page2").Select

With Sheets("Page1")
    s2rw = 3 ' Adjust to first data row #
    endcol = .Cells(s2rw - 1, 1).End(xlToRight).Column
    Do Until .Cells(s2rw, 1).Value = "" ' Loop through case #s
        s1rw = 0
        On Error Resume Next
        s1rw = Cells.Find(What:=.Cells(s2rw, 1).Value, LookIn:=xlFormulas, LookAt:=xlWhole).Row
        On Error GoTo 0
        
        If s1rw > 0 Then ' Found case #
            For col = 2 To endcol ' Loop through columns
                If Cells(s1rw, col).Value <> "" Then
                    If IsDate(Cells(s1rw, col).Value) Then
                        .Cells(s2rw, col).Value = Format(Cells(s1rw, col).Value, "mm/dd/yyyy")
                    Else
                        .Cells(s2rw, col).Value = Cells(s1rw, col).Value
                    End If
                End If
            Next
        End If
        s2rw = s2rw + 1
    Loop
    .Select
End With

However in page 1 I sometimes have formulas. I want my in page2 to copy over and clear the formula as the formula is then changing the pasted data.

Can anyone please help!!

[mikeTRON]

You want the formula pasted into sheet two, calculated THEN copy+paste values?

[TMS]

Why not just make a (full) copy of the source sheet, complete with formulae, then select all the cells and Copy and Paste Special | Values? Then you have an exact copy of the source data with no formulae, just values. And then do whatever processing is required on the target sheet with the values.

Regards, TMS