Hi All

I have a simpple player league table as shown below:

Pos Player Points
1 Tom 124
2 Linda 122
3 Harry 107
4 Jayne 100
4 Bob 100
5 Steve 89

I use the Sort method in the worksheet_change method to sort the table
as follows:
.Columns("B:C").Sort Key1:=Range("C2"), _
Order1:=xlDescending, _
Key2:=Range("B2"), _
Order2:=xlAscending, _
Header:=xlYes

This all works fine, but if I insert say a couple of new rows before
the table so that the Player and Points cells are at C4 and B4
respectivelly, even if I change the Range parameters in the sort call
to C4 and B4 the sort fails - seems to think I'm still using the
original locations(C2 and B2) - I've saved and reopend the worksheet
but still see the same behaviour.

Any ideas what I've done wrong ?

TIA
RH

Red, when you change the spreadsheet, the VBA code does not change, you
should go to the code and change manualy the order keys

hope it helps and thanks for the feedback
Regards from Brazil
Marcelo



"RedHook" escreveu:

> Hi All
>
> I have a simpple player league table as shown below:
>
> Pos Player Points
> 1 Tom 124
> 2 Linda 122
> 3 Harry 107
> 4 Jayne 100
> 4 Bob 100
> 5 Steve 89
>
> I use the Sort method in the worksheet_change method to sort the table
> as follows:
> .Columns("B:C").Sort Key1:=Range("C2"), _
> Order1:=xlDescending, _
> Key2:=Range("B2"), _
> Order2:=xlAscending, _
> Header:=xlYes
>
> This all works fine, but if I insert say a couple of new rows before
> the table so that the Player and Points cells are at C4 and B4
> respectivelly, even if I change the Range parameters in the sort call
> to C4 and B4 the sort fails - seems to think I'm still using the
> original locations(C2 and B2) - I've saved and reopend the worksheet
> but still see the same behaviour.
>
> Any ideas what I've done wrong ?
>
> TIA
> RH
>
>

Hi Marcelo

I did change the VBA code to reflect what I thought should be the new
Range values C4 and B4(i.e. the new cell positions of the table column
headers; Player and Points), but it makes no difference - I'm obviously
missing something basic here but I can't see what !

Regards
RH
Marcelo wrote:
> Red, when you change the spreadsheet, the VBA code does not change, you
> should go to the code and change manualy the order keys
>
> hope it helps and thanks for the feedback
> Regards from Brazil
> Marcelo
>
>
>
> "RedHook" escreveu:
>
> > Hi All
> >
> > I have a simpple player league table as shown below:
> >
> > Pos Player Points
> > 1 Tom 124
> > 2 Linda 122
> > 3 Harry 107
> > 4 Jayne 100
> > 4 Bob 100
> > 5 Steve 89
> >
> > I use the Sort method in the worksheet_change method to sort the table
> > as follows:
> > .Columns("B:C").Sort Key1:=Range("C2"), _
> > Order1:=xlDescending, _
> > Key2:=Range("B2"), _
> > Order2:=xlAscending, _
> > Header:=xlYes
> >
> > This all works fine, but if I insert say a couple of new rows before
> > the table so that the Player and Points cells are at C4 and B4
> > respectivelly, even if I change the Range parameters in the sort call
> > to C4 and B4 the sort fails - seems to think I'm still using the
> > original locations(C2 and B2) - I've saved and reopend the worksheet
> > but still see the same behaviour.
> >
> > Any ideas what I've done wrong ?
> >
> > TIA
> > RH
> >
> >