ExcelBanter - View Single Post

wynand

Thank you sandy!

"Sandy Mann" wrote:

You can't save it in your personal.xls because it needs to be in the
worksheet module of the workbook that you are using.

Right-click on the sheet tab and select View Code. Select the sheet that
you
want it to work in from the Project window if it is not already selected and
ensure that the left-hand window at the top of the module is saying
Worksheet. Delete everything in the module, it probably says:
*************************************
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

End Sub
**************************************

then copy and paste this macro into the Module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Change the ranges if required
If Intersect(Target, Range("E2:G13")) Is Nothing Then Exit Sub

Application.EnableEvents = False
Application.ScreenUpdating = False

On Error GoTo GetOut

Range("E14:G14").Copy

Range("E17").PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Range("E1:G1").Copy Destination:=Range("E16")

Application.CutCopyMode = False

Range("E16:G17").Sort Key1:=Range("E17"), _
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlLeftToRight
Range("E16").Select
GetOut:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Now when you enter any value in E2:G13 the Macro will kick in and sort the
totals under the table for you. If you enter data anywhere else then
nothing will happen.

The Macro assumes that the names are in Row 1 and the Totals are in Row 14.
If the ranges are not correct the change them but keep the quotation makes
around them.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

Replace @mailinator.com with @tiscali.co.uk

"wynand" wrote in message
...
Sandy

thanks, please explain the last step to make automatic.
Is there a way i can assign a short cut key to macro and save in
personal.xls for future use?

"Sandy Mann" wrote:

When you have created the list of totals using your formula there is no
longer a relationship between the totals and the names. You could do it
with formulas provided that there was *never* two totals of the same
amount.
As this is a very unlikely scenario I would suggest turning on the Macro
Recorder, copying the totals and names into two rows below the table and
sorting them left to right. To make the macro more automatic you could
paste it into a Worksheet_Change macro in the sheet module. Post back if
you need further help

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

Replace @mailinator.com with @tiscali.co.uk

"wynand" wrote in message
...
Hi
I have used large($e$14:$g$14,row(1:100) to sort sumtotals to new cells
below the table.
How can I sort the names belonging to these sumtotals (diferent cells
than
sumtotals) with these totals next to each other.
in other words i need not only the sums but also the names belonging to
the
totals to sort together