VBA to copy Filtered top 10 data to another sheet

[Turbo68]

I have this started with doing some google searches and the below code works but it is copying the wrong col in my original:

'Copy Sorted TOP 10

Dim i As Long
Dim r As Range
Dim rWC As Range

Set r = Range("D16", Range("D" & Rows.Count).End(xlUp)).SpecialCells(10)

For Each rWC In r
i = i + 1
If i = 10 Or i = r.Count Then Exit For
Next rWC
Range(r(1), rWC).Resize(, 10).SpecialCells(10).Copy


Sheets("Division TOP-BOTTOM 10").Select
Range("TOP_10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

My Data looks like this:

As you can see the A is St#, B is Area, C is blank, D is the COL i want it to highlight the top 10 entries. Right now the code i am using is highlighing this:

It is not selecting the visiable cells but it is selecting 10 cells but selecting A and B col and not D either! I thought i had r= row D in the setup but not a clue why its not selcting correctly.

Major confusion here...

Any help is much appreciated.

Mike

 

[Fluff]

What are you trying to do with this line?

Set r = Range("D16", Range("D" & Rows.Count).End(xlUp)).SpecialCells(10)

 

[Turbo68]

The code I found had that line in there. It made sense to me as to pointing the data that I needed. But, I don't follow it myself. It does specify the 10 lines of data that i need.

Mike

 

[Fluff]

That line of code is pointing to dependant cells.
Change the 10 to 12 on that line & the other specialcells line.

 

[Turbo68]

I will try that when I get back to the office and let you know... thanks!

 

[Fluff]

Ok.

 

[Turbo68]

Now the selection is this:

This is what i am needing it to select:

Maybe I have the total wrong coding? arrrggg

Mike

 

[Fluff]

I thought you wanted the top 10 visible rows from column D. If that is not the case, then please clearly explain exactly what you do want.

 

[Turbo68]

Sorry to be confusing:

As you see here... i am looking for the top 10 in col D to copy:

Is that better?

M

 

[Fluff]

If you made the 2 changes I suggested, it should do that.
Can you please post your current code?