Auto sort

Steve Bowen

New Member
Joined
Oct 12, 2006
Messages
7
is there any automatic way of displaying the top 10 (lets say accounts) from data in another sheet within the same workbook ?
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

hayden

Board Regular
Joined
Sep 23, 2005
Messages
159
Welcome to the board.

Can you elaborate a bit more on your question. Such as what defines a top account? Where are the data located, etc. I am sure we can get an answer for you.

Hayden
 

Steve Bowen

New Member
Joined
Oct 12, 2006
Messages
7
Thank you for the speedy reply

OK to simplify things , lets say I have a workbook with 2 sheets

one sheet (lets call it raw data) has account names and 12 columns showing sales by month plus a total column summing the year

on the second sheet, I would like to show the top ten dollar producing accounts in descending order ONLY, of course taken from the yearly totals by account on the raw data sheet.

I hope thats enough info. Its not exactly that simple but I am sure I can adjust it once I get an idea of what we are doing here to generate the info

Thanking you so much

Steve
 

hayden

Board Regular
Joined
Sep 23, 2005
Messages
159
A couple of options then.

You could sort the data and copy it over. (the manual way) or we could write a macro to sort the data and then copy it over to the other sheet with a push of a button.

Is it always the top 10? And is the raw data the same every time or does it change i.e 200 accounts once, then 150 the next time, then 175 the next time?

You might try recording a macro in excel of what you want to do, then editing it from there.

Let us know.

Hayden
 

Steve Bowen

New Member
Joined
Oct 12, 2006
Messages
7
Thank you Hayden, I feel so stupid, it never even crossed my mind to macro it. I did write the following maco and it works fine BUT it locks the workbook for editing and cant be saved again as the original name only copy of. Can you see anything wrong with this macro that would do that:

Sub Top10()
'
' Top10 Macro
' Macro recorded 10/13/2006 by sescolit-xl5yi5
'

'
Sheets("Details").Select
Range("A6:A57").Select
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
Range("A6:A57,B6:B57").Select
Range("B6").Activate
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
Range("A6:A57,B6:B57,D6:D57").Select
Range("D6").Activate
Selection.Copy
Sheets("Top 10 Customers").Select
ActiveWindow.SmallScroll ToRight:=17
Range("AB3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("AD4"), Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("AB7:AD16").Select
Selection.Copy
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("C8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Interior.ColorIndex = xlNone
Selection.Font.ColorIndex = 1
Selection.Font.Bold = False
Selection.Font.Bold = True
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Overview").Select
End Sub
 

jmckeone

Well-known Member
Joined
Jun 3, 2006
Messages
550
the ActiveWindow.ScrollColumn rows can all be deleted as they are not essential to the functioning of the macro.
 

Forum statistics

Threads
1,141,095
Messages
5,704,311
Members
421,338
Latest member
Pepess

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top