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