Excel 2k VBA problem - For loops interating a sort

[robert_m_mills]

Running MS Office 2000, SP3 on NT 4 SP6

Goal: Given a workbook in which worksheet 2, "Data" has the form:
H T U
Account Name Jan 04 Feb 04
account name1 #s for jan #s for feb
account name2 ...
account name1
account name4
...

this does the full year so it goes to AE.

I'm trying to have a macro calculate the top 10 and top 20 for 'accoun
name1' per month and store them in worksheet 3, "Derived" in whic
account name1's top 10 for column T go into B8 and the top 20 into b9
then the top 10/20 for column U go into c8,c9 &c.

I can sort by account name1 and one of the columns but it would hurt m
to use copy and paste 12 times instead of a for loop! I'm brand new t
VBA (started yesterday, got this far from reading previous posts o
this forum) and really appreciate your help!

Thanks in advance,

Rob

Code:
Sub TopTenTwenty()
i = 2
For j = 20 To 31
ActiveWorkbook.Sheets("Data").Activate
Sheets("Data").Range("A:AE").Sort _
Key1:=Worksheets("Data").Columns("H"), _
Order1:=xlAscending, _
Key2:=Worksheets("Data").Columns(j), _
Order2:=x1Descending, _
Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

'Takes top ten/twenty
ActiveWorkbook.Sheets("Derived").Activate
ActiveSheet.Cells(8, i).Value
WorksheetFunction.Sum(Worksheets("Data").Range(Cells(2, j), Cells(11
j)))
ActiveSheet.Cells(9, i).Value
WorksheetFunction.Sum(Worksheets("Data").Range(Cells(2, j), Cells(21
j)))
i = i + 1
Next j
End Su

 

[JWolf]

Look up the LARGE function in XL help.

 

[robert_m_mills]

wouldn't that mean reading each column into the array and then callin
large 20 times while adding the value to an accumulator and writing i
@ 10 values and 20 values

 

[JWolf]

yeah it would. It looks like it would be better to use Pivot Tables
instead of VBA.