Copy & Paste Visible cells only top 10 filtered range

[harinsh]

Hi Team,

Can anyone please help me out how to select top 10 visible range of cells those were filtered and copy paste the same data into different sheet. I need to update this code to updated in vba macro code. Please provide an resolution in order to fix this.

NOte: Data is dynamic and it will change every time which update.

Thanks in advance.

 

[Grazier]

Hello Harinsh,

There is not much information, but here is a very simple peace of code which uses top10 filtering and copies the data to sheet2.

You need to modify it a little.

Sub Test()
Dim X As Range
Dim Y As Range
Set X = Columns("A:IV")
Set Y = Range("1:11")
Sheets("Sheet1").Activate
X.Select
Selection.AutoFilter Field:=3 'Not needed, just resets the filter if the macro is used several times in a row
Selection.AutoFilter Field:=3, Criteria1:="10", Operator:=xlTop10Items 'Insert the correct filter info
Cells.Copy
Sheets("Sheet2").Activate
ActiveSheet.Paste
End Sub

-Grazier

 

[harinsh]

Hi Grazier,

I'm really sorry, not able to fulfill my requirement with given code. Let me explain in details with an example.

I have data with lots of columns assume A to F. I would like to sort with total (Decending order) and if 'Zero' comes in any top 10 cells and then that row should not reflect for calculation (can filter it in macro). Later on I need to copy top 10 (after excluding 'Zero' contain rows) and paste it into different sheet.

It should get copy & paste only visible cells. Please provide your suggestion how better way to do it this and also help with codes.

Thanks,

[TABLE="width: 372"]
<colgroup><col><col span="5"></colgroup><tbody>[TR]
[TD]Month
[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]AAAAA[/TD]
[TD="align: right"]513[/TD]
[TD="align: right"]434[/TD]
[TD="align: right"]523[/TD]
[TD="align: right"]513[/TD]
[TD="align: right"]1983[/TD]
[/TR]
[TR]
[TD]BBBBB[/TD]
[TD="align: right"]462[/TD]
[TD="align: right"]713[/TD]
[TD="align: right"]201[/TD]
[TD="align: right"]462[/TD]
[TD="align: right"]1838[/TD]
[/TR]
[TR]
[TD]CCCCC[/TD]
[TD="align: right"]577[/TD]
[TD="align: right"]223[/TD]
[TD="align: right"]549[/TD]
[TD="align: right"]577[/TD]
[TD="align: right"]1926[/TD]
[/TR]
[TR]
[TD]DDDDD[/TD]
[TD="align: right"]620[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]375[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]995[/TD]
[/TR]
[TR]
[TD]EEEEEE[/TD]
[TD="align: right"]693[/TD]
[TD="align: right"]233[/TD]
[TD="align: right"]292[/TD]
[TD="align: right"]693[/TD]
[TD="align: right"]1911[/TD]
[/TR]
[TR]
[TD]FFFFFF[/TD]
[TD="align: right"]278[/TD]
[TD="align: right"]338[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]278[/TD]
[TD="align: right"]894[/TD]
[/TR]
[TR]
[TD]GGGGG[/TD]
[TD="align: right"]481[/TD]
[TD="align: right"]343[/TD]
[TD="align: right"]333[/TD]
[TD="align: right"]481[/TD]
[TD="align: right"]1638[/TD]
[/TR]
[TR]
[TD]HHHHH[/TD]
[TD="align: right"]416[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]416[/TD]
[TD="align: right"]1232[/TD]
[/TR]
[TR]
[TD]IIIIIIIIII[/TD]
[TD="align: right"]369[/TD]
[TD="align: right"]145[/TD]
[TD="align: right"]442[/TD]
[TD="align: right"]369[/TD]
[TD="align: right"]1325[/TD]
[/TR]
[TR]
[TD]JJJJJJJJ[/TD]
[TD="align: right"]390[/TD]
[TD="align: right"]336[/TD]
[TD="align: right"]219[/TD]
[TD="align: right"]390[/TD]
[TD="align: right"]1335
[/TD]
[/TR]
</tbody>[/TABLE]