Sorting VBA problem

[wynand]

Please help!
The problem code is as follows::

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

Application.EnableEvents = False
Application.ScreenUpdating = False

On Error GoTo GetOut

Range("D21:F21,I21:K21,N2121,D36:F21,I36:K21,N3621").Copy

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

Range("B10,G10,L10,B25,G25,L25").Copy Destination:=Range("S29")

Application.CutCopyMode = False

Range("S29:V34").Sort Key1:=Range("S29"), _
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlLeftToRight

Target.Offset(0, 1).Select
GetOut:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

I received and adapted another code to fit my range.
The code is not working. I'm trying to automaticly sort three sumtotals in
different cell references (D2121 etc) to the relevant names (B10,G10 etc)
in a different location on the same sheet.

 

[Bernie Deitrick]

Your code is confused - you have ranges stated backwards (I36:K21 is usually given I21:K36), you
have overlapping ranges of different size: for example, this will error:

Range("D21:F21,I21:K21,N2121,D36:F21,I36:K21,N3621").Copy

but if rewritten this way it won't error:

Range("D21:F36,I21:K36,N2136").Copy

and then you paste values over a previous paste range (Range("T29:V21").PasteSpecial ), sort based
on an incomplete range, etc.

It would be better if you described in words what you want. Try to be specific.

HTH,
Bernie
MS Excel MVP

 

[wynand]

The data is score tables on one sheet.
B10-F10, G10-K10, L10-P10, B25-F25,G25-K25, and L25-P25 are the names of
individuals. cells are merged (b10-f10 etc) and =6 individuals
B14-F20,G14-F20, L14-P20,B29-F35,G29-K35 and L29-P35 is the scores.
three of these scores are sumtotaled in D21-F21, I21-K21, N21-P21,
D36-F36,I36-K36 and n36-p36.
The automatic sorting should take place in S29 S34 (Names of individuals)and
T29 to v34 (three sumtotals of individuals)
When number or score is changed in the range the data is updated and sorted
per scores automatically
Usually this is done manually by coppying and pasting names in cells (S) and
then pasting the sumtotals in T and then everything is highlighted and sorted
by preference of the totals. A recored macro does not shorten the process
E.g.

Jack John
111 111 111 100 50 111 111 110 99 10
111 111 111 100 50 111 111 110 99 10
222 200 100 220 198 20


Jack 222 100 50
John 250 110 20 etc...

 

[Bernie Deitrick]

Which column will you use to sort the resulting table? S (names) T, U, or V (scores)? Your code
shows sorting based on names, but that doesn't seem like it would change, so there would be little
need to do that.

Jack 222 100 50
John 250 110 20 etc...

The only descending column is V - is that what you want?

HTH,
Bernie
MS Excel MVP

 

[wynand]

First T then U and then V. The names should sort with the totals when values
are changed in the tables.
In other words this is a template sheet, but individuals' scores would
change and therefore their ranking as per S,T,U an V with the names. Changes
in scores would the rankings with all associated data.

 

[Bernie Deitrick]

The easiest thing to do is to set up your data table with links to the values of interest - BUT use
absolute addressing. When you sort, the links stay with the intended cell after the sort. So use,
for example:

=$B$10

NOT

=B10

for all the links, then when you sort, you'll be fine. Record a macro performing the sorting that
you want, and you'll be able to use the exact code that your record.

HTH,
Bernie
MS Excel MVP

 

[wynand]

thank you it works!

The easiest thing to do is to set up your data table with links to the values of interest - BUT use
absolute addressing. When you sort, the links stay with the intended cell after the sort. So use,
for example:

=$B$10

NOT

=B10

for all the links, then when you sort, you'll be fine. Record a macro performing the sorting that
you want, and you'll be able to use the exact code that your record.

HTH,
Bernie
MS Excel MVP