xlPasteValue not working with Copy Destination script-CodePudding

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! ^.^

CodePudding user response：

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