help copying cell value instead of formula

I'm using this code to copy selected rows to another sheet but I want to just copy the value and not the formula. I'm useing "Copy Destination:=ws1.Cells" but is there a way to paste value?

Formula:

Private Sub commandbutton2_click()



'adds selected rows to information that already exists on protocol sheet - instead of overwriting them

    Dim i As Integer

    Dim j As Integer

    Dim aRow As Long

    Dim jRow As Long

    Dim lRow As Long

    Dim ws As Worksheet

    Dim ws1 As Worksheet

    Set ws1 = Sheets("protocol")

    Set ws = Sheets("coherence")

    aRow = ws.Cells(Rows.Count, "a").End(xlUp).Row ' how many selections in column A

    jRow = ws1.Cells(Rows.Count, "l").End(xlUp).Row ' how many rows are already filled in protocol sheet

    lRow = ws.Cells(Rows.Count, "l").End(xlUp).Row 'how many selections in column l

    j = jRow + 1 ' which row to place data on protocol sheet

   

    

         With ws

            For i = 3 To aRow

                 If .Cells(i, 1) = "X" Then 'where to look for true - offset one right from linked cell of toggle switch - coherence

                .Cells(i, 2).Resize(, 9).Copy Destination:=ws1.Cells(j, 12)  ' start pasting cells at J down, 12 across

                 j = j + 1

                 End If

            Next i

            For i = 3 To lRow

                 If .Cells(i, 12) = "X" Then 'where to look for true - offset one right from linked cell of toggle switch - coherence

                .Cells(i, 13).Resize(, 9).Copy Destination:=ws1.Cells(j, 12) ' start pasting cells at J down, 12 across

                 j = j + 1

                 End If

            Next i

        End With

  Worksheets("protocol").Range("l5:t100").RemoveDuplicates Columns:=Array(1, 1)

   

   

        

End Sub

To paste values only, you will need 2 lines for the copy/paste. For example:

.Cells(i, 2).Resize(, 9).Copy Destination:=ws1.Cells(j, 12)

becomes:

.Cells(i, 2).Resize(, 9).Copy
ws1.Cells(j, 12).PasteSpecial xlPasteValues

Alternatively, keep the code the way you have it, and just make the value of the range in question = the .Value of the range... for example if A1 to A6 are the result of a formula, then Range("A1:A6").Value = Range("A1:A6").Value would convert the formulas to values

Glad I could help
Thanks for the rep, and for marking the thread as solved.

I'm getting an error but I think im supposed to post a new thread instead of continuing on this one so I'll do that.

Edit: problem solved. I noticed that the second For statement worked fine so I just erased the first For statement and rewrote the second to match the first then rewrote the second and now it all works. Seems nonsensical but somehow it did the trick. In the end its the exact same code as above. Here is the new code with the paste function.

Formula:

rivate Sub commandbutton2_click()



'adds selected rows to information that already exists on protocol sheet - instead of overwriting them

    Dim i As Integer

    Dim j As Integer

    Dim aRow As Long

    Dim jRow As Long

    Dim lRow As Long

    Dim ws As Worksheet

    Dim ws1 As Worksheet

    Set ws1 = Sheets("protocol")

    Set ws = Sheets("coherence")

    aRow = ws.Cells(Rows.Count, "a").End(xlUp).Row ' how many selections in column A

    jRow = ws1.Cells(Rows.Count, "l").End(xlUp).Row ' how many rows of are already filled in protocol sheet

    lRow = ws.Cells(Rows.Count, "l").End(xlUp).Row 'how many selections in column l

    j = jRow + 1 ' which row to place data on protocol sheet

   

    

         With ws

           For i = 3 To aRow

                 If .Cells(i, 1) = "X" Then 'where to look for true - offset one right from linked cell of toggle switch - coherence

                    .Cells(i, 2).Resize(, 9).Copy 'Destination:=ws1.Cells(j, 12)

                    ws1.Cells(j, 12).PasteSpecial xlPasteValues ' start pasting cells at J down, 12 across

                 j = j + 1

                 End If

            Next i

            For i = 3 To lRow

                If .Cells(i, 12) = "X" Then

                    .Cells(i, 13).Resize(, 9).Copy

                    ws1.Cells(j, 12).PasteSpecial xlPasteValues

                j = j + 1

                End If

            Next i

        End With

  Worksheets("protocol").Range("l5:t100").RemoveDuplicates Columns:=Array(1, 1)

   

   

        

End Sub