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
Bookmarks