Filter Value from Column and paste selection as value in another Sheet VBA

[vradhak7]

VBA Experts,
I have a source sheet and a destination sheet. In the "source" sheet, I have columns A to F. I want to filter column F and select anything <=60% and select the displayed range and paste as value in sheet "Destination".
After doing this, it has to go back to "Source sheet" and unfilter the selection. How can I do this using VBA? Your expertise is appreciated. I have attached the file for reference.

[vcoolio]

Hello Vradhak7,

It appears from your sample that you are actually filtering on Column D. Hence, try the following code:-

Sub TransferData()

Application.ScreenUpdating = False

With Sheet1.Range("D3", Sheet1.Range("D" & Sheet1.Rows.Count).End(xlUp))
     .AutoFilter 1, "<=60%"
     .Offset(1).EntireRow.Copy
     Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
     .AutoFilter
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

I hope that this helps.

Cheerio,
vcoolio.

[mdmackillop]

Sub Test()
    col = "F"
    crit = "<=60%"
    
With Sheet1
        With Range(.Cells(3, col), .Cells(Rows.Count, col).End(xlUp))
        .AutoFilter
        .AutoFilter 1, crit
        Sheet1.UsedRange.Copy
        .AutoFilter
        Sheet2.Range("A1").PasteSpecial xlPasteAll
        Application.Goto Sheet2.Range("A1")
        End With
    End With
    Application.CutCopyMode = False
End Sub