How to copy formula result

Question

How do I copy a formula result?

I select which rows to keep in the worksheet "UI", by marking the rows with the value 1 in column B.

I assigned the following macro to a command button, which copies the selected rows to the worksheet "Output":

Private Sub CommandButton1_Click()
    
    Dim i As Integer
    Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("UI")
    Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("Output")
    
    For i = 2 To ws1.Range("B999").End(xlUp).Row
        If ws1.Cells(i, 2) = "1" Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 2).End(xlUp).Row + 1)
    Next i
End Sub

As the values in the rows are the results of formulas, the results pasted in "Output" come back as invalid cell references.

Is there a way of copy-pasting as text?

Answer 1

You should use "xlPasteValues" property to avoid invalid cell references when values in the rows are the results of formulas. You can try to modify your code as follows:

Private Sub CommandButton1_Click()
    
    Dim i As Integer
    Dim ws1 As Worksheet: Set ws1 = Sheets("UI")
    Dim ws2 As Worksheet: Set ws2 = Sheets("Output")
    
    For i = 2 To ws1.Range("B999").End(xlUp).Row
        If ws1.Cells(i, 2) = "1" Then
             ws1.Rows(i).Copy
             ws2.Rows(ws2.Cells(ws2.Rows.Count, 2).End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteValues
        End If
    Next i
    
End Sub