Copying only filtered data to another cell

[afc171]

Hi all,

I am trying to filter the last column F12 (with Y) and copying the showing data with Y and pasting into cells A3 to E10 (only allowing 8 rows)

In the example it shows rows 16, 20, 36 and 45 to be copied, but next time this might be different depending on the stock checked in column F12.

How can I copy the data only filtered from whatever is visible?

Thank you

 

[igold]

If you are ok with copying values only, how about this...

Sub CopyFilterRng()

    Dim rng

    rng = ActiveSheet.AutoFilter.Range.Offset(1, 0)
    Range("A3").Resize(8, 5) = rng
        
End Sub

 

[afc171]

Thanks @igold

That seems to copy the data from the top of list, not the filtered cells... ?

 

[igold]

Worked for me with a test mock up. Did you clear the contents on top before you ran the code...

It does not appear that the bottom half has filter applied to it on this second picture that you supplied...

 

[afc171]

I just posted that screenshot to show what copied to sell A3..

Yes cleared the contents, apply filter in cell F12 then run the VBA and get what is in the attached image.

So its copying the top 8 rows from B13 not the filtered "y" rows

 

[igold]

My bad. Yes, this code is not working as intended.

 

[igold]

Sorry about my previous faux pas. How about this. You should know that this code will create and delete a temporary "helper" sheet, but it does fill your requirements...

Sub CopyFilterRng()

    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim rng As Range
    Dim arr
    
    Application.ScreenUpdating = False
    Set rng = ws.AutoFilter.Range.Offset(1, 0)
    rng.Copy
    Worksheets.Add.Name = "tmp"
    Range("A1").PasteSpecial Paste:=xlPasteValues
    arr = Worksheets("tmp").Range("A1:F8")
    Application.DisplayAlerts = False
    Worksheets("tmp").Delete
    Application.DisplayAlerts = True
    ws.Range("A3").Resize(8, 6) = arr
    Application.ScreenUpdating = True
            
End Sub

I hope this helps...