This code works as expected to copy the cells given a value of "xxx" in column B. The issue is that it copies the entire row contents, including formulas. I would only like to copy the cell values and formatting, not formulas.
Sub CommandButton1_Click()
Dim LastRow As Long
Dim i As Long, j As Long
'Find the last used row in a Column: column A in this example (source sheet = sheet2)
With Worksheets("Sheet2")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
'Message box to confirm how many rows were scanned to ensure all rows were scanned
MsgBox ("Number of rows scanned: " & LastRow)
'First row number where you need to paste values in Sheet3 (destination sheet = sheet3)'
With Worksheets("Sheet3")
j = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With
For i = 1 To LastRow
With Worksheets("Sheet2")
If .Cells(i, 2).Value = "xxx" Then
.Rows(i).Copy Destination:=Worksheets("Sheet3").Range("A" & j)
j = j + 1
End If
End With
Next i
End Sub
I've tried amending the last portion to read like
.Rows(i).Copy
.Range("A" & j).PasteSpecial xlPasteValuesAndNumberFormats
However that attempts to paste the rows in the same worksheet (probably because it's under "With"). I haven't been able to change the destination of the pasting of the rows. Ideally I would like the copied rows to be pasted into Sheet3.