xlPasteValue not working with Copy Destination script

July 15, 2022

having trouble getting this script to copy the values in Source Column "H", rather than the formulas.

With Sheets("Sheet")
With .Range("H8", .Range("H" & Rows.Count).End(xlUp))
.AutoFilter Field:=1, Criteria1:=">0"
If .SpecialCells(xlVisible).Count > 1 Then .Offset(1).Resize(.Rows.Count - 1).Copy Destination:=Sheets("Register").Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
.AutoFilterMode = False
End With

I’ve tried attaching .PasteSpecial xlPasteValues to the end of it, but it’s not working.
Would love some help! ^.^

>Solution :

make it two lines:

With Sheets("Sheet")
    With .Range("H8", .Range("H" & Rows.Count).End(xlUp))
        .AutoFilter Field:=1, Criteria1:=">0"
        If .SpecialCells(xlVisible).Count > 1 Then
             .Offset(1).Resize(.Rows.Count - 1).Copy
             Sheets("Register").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        End If
     End With
     .AutoFilterMode = False
End With