Automate capturing a particular columns using VBA

[Kaniguan1969]

Hi,
I have an excel that i would like to automate capturing the particular columns and at the same time
replicate to another worksheet. Other requirements is to get the top 3 items per model using the qty
as reference. THank you in advance.

Raw Data:

Columns----A-----B-------C-------D---------E----------F-----------G--------H------------I--------J---------K--------L--------M--------N--------O------P----Q
Header ---Name--MI--Surename--Model---SKU--------Itemane----Location--- Description----------------------------- Qty----change---reason---details
Details---xx----x---xxxx ----Motion--xxxx-----------xxx---------xxx------xxxxx------------------------------------150-------xx------xxx------xxx
----------xx----x---xxxx ----Motion--xxxx-----------xxx---------xxx------xxxxx------------------------------------100-------xx------xxx------xxx
----------xx----x---xxxx ----Motion--xxxx-----------xxx---------xxx------xxxxx------------------------------------75--------xx------xxx------xxx
----------xx----x---xxxx ----Motion--xxxx-----------xxx---------xxx------xxxxx------------------------------------200--------xx------xxx------xxx
----------xx----x---xxxx ----Motion--xxxx-----------xxx---------xxx------xxxxx------------------------------------35--------xx------xxx------xxx
----------zz----z---zzzz ----RED-----zzzz-----------zzz---------zzz------zzzzz------------------------------------50--------zz------zzz------zzz
----------zz----z---zzzz ----RED-----zzzz-----------zzz---------zzz------zzzzz------------------------------------1000------zz------zzz------zzz
----------zz----z---zzzz ----RED-----zzzz-----------zzz---------zzz------zzzzz------------------------------------1500------zz------zzz------zzz
----------zz----z---zzzz ----RED-----zzzz-----------zzz---------zzz------zzzzz------------------------------------500-------zz------zzz------zzz
----------zz----z---zzzz ----RED-----zzzz-----------zzz---------zzz------zzzzz------------------------------------250-------zz------zzz------zzz
----------zz----z---zzzz ----RED-----zzzz-----------zzz---------zzz------zzzzz------------------------------------125-------zz------zzz------zzz

Desired Result:

Model-----sku------description-------Qty----Change--reason--details--Line#
---------------------------------------------------------------------------
Motion----xxxx-----------xxxxx------- 200-----xx------xx------xx------1
Motion----xxxx-----------xxxxx------- 150-----xx------xx------xx------2
Motion----xxxx-----------xxxxx------- 100-----xx------xx------xx------3
RED-------zzzz-----------zzzzz------- 1500----zz------zz------zz------1
RED-------zzzz-----------zzzzz------- 1000----zz------zz------zz------2
RED-------zzzz-----------zzzzz------- 500-----zz------zz------zz------3

[Kaniguan1969]

Upon searching from the net i got this codes and its working.
My concern is how could i incorporate the codes to select the top 3 values for each model using the qty.

Here is the code:


Dim my_range As Range
Dim ws As Worksheet
Set my_range = ThisWorkbook.Sheets("Calculation1").Range("D4,E4,J4,L4,M4,N4")
Set ws = ThisWorkbook.Sheets("Sheet3")
Do
If Application.WorksheetFunction.CountA(my_range) > 0 Then
my_range.Copy ws.Range("A" & ws.Rows.Count).End(xlUp).Offset(1, 0)
Set my_range = my_range.Offset(1, 0)
Else
Exit Do
End If
Loop

[Kaniguan1969]

Btw, my new requirement is how to find the top 3 values for each model.
I'm using a useform with button to perform this task. I would like to copied
the result to another worksheet. My raw data is located undersheet("data") while
the result would be copied to sheet("Report"). It is under one workbook.


Desired Result:


Model-----sku------description-------Qty----Change--reason--details--Line#
---------------------------------------------------------------------------
Motion----xxxx-----------xxxxx------- 200-----xx------xx------xx------1
Motion----xxxx-----------xxxxx------- 150-----xx------xx------xx------2
Motion----xxxx-----------xxxxx------- 100-----xx------xx------xx------3
RED-------zzzz-----------zzzzz------- 1500----zz------zz------zz------1
RED-------zzzz-----------zzzzz------- 1000----zz------zz------zz------2
RED-------zzzz-----------zzzzz------- 500-----zz------zz------zz------3