I have a worksheet that updates from another worksheet. I need the second
worksheet to automatically sort in descending order.

Example:
Cols A-B-C-D updates Cols F-G-H-I

A B C D updates F G H
I
name Date Gm Score name date GM Score
Tm 1 1-07 Gm1 200 Tm 1 1-14 Gm2 500
Tm 1 1-07 Gm2 150 Tm 1 1-14 Gm1 300
Tm 1 1-14 GM1 300 Tm 1 1-07 Gm1 200
Tm 1 1-14 GM2 500 Tm 1 1-07 Gm2 150

Once data is updated, I need it to sort in descending order automatically.

Thanks for any assistance in resolving this.

First enter this macro:

Sub gayle()
Set r1 = Sheets("s1").Range("A:D")
Set r2 = Sheets("s2").Range("F1")
r1.Copy r2
Sheets("s2").Activate
Range("F:I").Sort Key1:=Range("I1"), Order1:=xlDescending
End Sub

Once data in sheet s1 has been updated, run the macro. It will first copy
the data to columns F thru I in sheet s2 and then sort that data by the
values in column I
--
Gary''s Student - gsnu200728


"Gayle C" wrote:

I have a worksheet that updates from another worksheet. I need the second
worksheet to automatically sort in descending order.

Example:
Cols A-B-C-D updates Cols F-G-H-I

A B C D updates F G H
I
name Date Gm Score name date GM Score
Tm 1 1-07 Gm1 200 Tm 1 1-14 Gm2 500
Tm 1 1-07 Gm2 150 Tm 1 1-14 Gm1 300
Tm 1 1-14 GM1 300 Tm 1 1-07 Gm1 200
Tm 1 1-14 GM2 500 Tm 1 1-07 Gm2 150

Once data is updated, I need it to sort in descending order automatically.

Thanks for any assistance in resolving this.

Thanks, Gary's Student . . I think I got the macro to work, but I have to
click on the button, each time, to get results. I need for it to update
"automatically", each time data is entered, without clicking on the button.

Is there a way to program the macro to run automatically?
Thanks,
Gayle C

"Gary''s Student" wrote:

First enter this macro:

Sub gayle()
Set r1 = Sheets("s1").Range("A:D")
Set r2 = Sheets("s2").Range("F1")
r1.Copy r2
Sheets("s2").Activate
Range("F:I").Sort Key1:=Range("I1"), Order1:=xlDescending
End Sub

Once data in sheet s1 has been updated, run the macro. It will first copy
the data to columns F thru I in sheet s2 and then sort that data by the
values in column I
--
Gary''s Student - gsnu200728


"Gayle C" wrote:

I have a worksheet that updates from another worksheet. I need the second
worksheet to automatically sort in descending order.

Example:
Cols A-B-C-D updates Cols F-G-H-I

A B C D updates F G H
I
name Date Gm Score name date GM Score
Tm 1 1-07 Gm1 200 Tm 1 1-14 Gm2 500
Tm 1 1-07 Gm2 150 Tm 1 1-14 Gm1 300
Tm 1 1-14 GM1 300 Tm 1 1-07 Gm1 200
Tm 1 1-14 GM2 500 Tm 1 1-07 Gm2 150

Once data is updated, I need it to sort in descending order automatically.

Thanks for any assistance in resolving this.

Sure. Let's make the assumption that as data is entered or modified in s1,
the last item changed in any row is in column D. This sub will automatically
call sub gayle whenever data is entered into column D or data is changed in
column D:


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D:D")) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Call gayle
Application.EnableEvents = True
End Sub

This is worksheet code, it is very easy to install and automatic to use:

1. from Excel (s1), right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window


--
Gary''s Student - gsnu200728


"Gayle C" wrote:

Thanks, Gary's Student . . I think I got the macro to work, but I have to
click on the button, each time, to get results. I need for it to update
"automatically", each time data is entered, without clicking on the button.

Is there a way to program the macro to run automatically?
Thanks,
Gayle C

"Gary''s Student" wrote:

First enter this macro:

Sub gayle()
Set r1 = Sheets("s1").Range("A:D")
Set r2 = Sheets("s2").Range("F1")
r1.Copy r2
Sheets("s2").Activate
Range("F:I").Sort Key1:=Range("I1"), Order1:=xlDescending
End Sub

Once data in sheet s1 has been updated, run the macro. It will first copy
the data to columns F thru I in sheet s2 and then sort that data by the
values in column I
--
Gary''s Student - gsnu200728


"Gayle C" wrote:

I have a worksheet that updates from another worksheet. I need the second
worksheet to automatically sort in descending order.

Example:
Cols A-B-C-D updates Cols F-G-H-I

A B C D updates F G H
I
name Date Gm Score name date GM Score
Tm 1 1-07 Gm1 200 Tm 1 1-14 Gm2 500
Tm 1 1-07 Gm2 150 Tm 1 1-14 Gm1 300
Tm 1 1-14 GM1 300 Tm 1 1-07 Gm1 200
Tm 1 1-14 GM2 500 Tm 1 1-07 Gm2 150

Once data is updated, I need it to sort in descending order automatically.

Thanks for any assistance in resolving this.

Thanks again! I apologize for the confusion. . I'm very new to Macros. You
are very patient to take the time to work with a novice.

I tried the one you sent but didn't totally understood your terminology.
So when I ran it, I got "run-time error '9', subscript out of range". So I
tried one I'd previously created (which worked but wouldn't auto sort) and
added your info. (my orginal Macro is shown below-gayle2)

From your original explanation, if I understand it, you are moving a,b,c,d
column data from 1 worksheet to f,g,h,i on another sheet. They are actually
on the same sheet. The a.b.c.d data originates from another sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D:D")) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Call gayle2
Application.EnableEvents = True

End Sub
Sub gayle2()
'
' gayle2 Macro
' Macro recorded 6/10/2007 by *
'

'
Range("A11:D218").Select
Selection.Copy
Range("F11").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("I11"), Order1:=xlDescending, Key2:=Range( _
"G11"), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
End Sub

"Gary''s Student" wrote:

Sure. Let's make the assumption that as data is entered or modified in s1,
the last item changed in any row is in column D. This sub will automatically
call sub gayle whenever data is entered into column D or data is changed in
column D:


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D:D")) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Call gayle
Application.EnableEvents = True
End Sub

This is worksheet code, it is very easy to install and automatic to use:

1. from Excel (s1), right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window


--
Gary''s Student - gsnu200728


"Gayle C" wrote:

Thanks, Gary's Student . . I think I got the macro to work, but I have to
click on the button, each time, to get results. I need for it to update
"automatically", each time data is entered, without clicking on the button.

Is there a way to program the macro to run automatically?
Thanks,
Gayle C

"Gary''s Student" wrote:

First enter this macro:

Sub gayle()
Set r1 = Sheets("s1").Range("A:D")
Set r2 = Sheets("s2").Range("F1")
r1.Copy r2
Sheets("s2").Activate
Range("F:I").Sort Key1:=Range("I1"), Order1:=xlDescending
End Sub

Once data in sheet s1 has been updated, run the macro. It will first copy
the data to columns F thru I in sheet s2 and then sort that data by the
values in column I
--
Gary''s Student - gsnu200728


"Gayle C" wrote:

I have a worksheet that updates from another worksheet. I need the second
worksheet to automatically sort in descending order.

Example:
Cols A-B-C-D updates Cols F-G-H-I

A B C D updates F G H
I
name Date Gm Score name date GM Score
Tm 1 1-07 Gm1 200 Tm 1 1-14 Gm2 500
Tm 1 1-07 Gm2 150 Tm 1 1-14 Gm1 300
Tm 1 1-14 GM1 300 Tm 1 1-07 Gm1 200
Tm 1 1-14 GM2 500 Tm 1 1-07 Gm2 150

Once data is updated, I need it to sort in descending order automatically.

Thanks for any assistance in resolving this.

By any chance, are there repeated values in Column D??

If there are no repeated values, then we can make this work without any
macros !
--
Gary''s Student - gsnu200728


"Gayle C" wrote:

Thanks again! I apologize for the confusion. . I'm very new to Macros. You
are very patient to take the time to work with a novice.

I tried the one you sent but didn't totally understood your terminology.
So when I ran it, I got "run-time error '9', subscript out of range". So I
tried one I'd previously created (which worked but wouldn't auto sort) and
added your info. (my orginal Macro is shown below-gayle2)

From your original explanation, if I understand it, you are moving a,b,c,d
column data from 1 worksheet to f,g,h,i on another sheet. They are actually
on the same sheet. The a.b.c.d data originates from another sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D:D")) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Call gayle2
Application.EnableEvents = True

End Sub
Sub gayle2()
'
' gayle2 Macro
' Macro recorded 6/10/2007 by *
'

'
Range("A11:D218").Select
Selection.Copy
Range("F11").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("I11"), Order1:=xlDescending, Key2:=Range( _
"G11"), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
End Sub

"Gary''s Student" wrote:

Sure. Let's make the assumption that as data is entered or modified in s1,
the last item changed in any row is in column D. This sub will automatically
call sub gayle whenever data is entered into column D or data is changed in
column D:


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D:D")) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Call gayle
Application.EnableEvents = True
End Sub

This is worksheet code, it is very easy to install and automatic to use:

1. from Excel (s1), right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window


--
Gary''s Student - gsnu200728


"Gayle C" wrote:

Thanks, Gary's Student . . I think I got the macro to work, but I have to
click on the button, each time, to get results. I need for it to update
"automatically", each time data is entered, without clicking on the button.

Is there a way to program the macro to run automatically?
Thanks,
Gayle C

"Gary''s Student" wrote:

First enter this macro:

Sub gayle()
Set r1 = Sheets("s1").Range("A:D")
Set r2 = Sheets("s2").Range("F1")
r1.Copy r2
Sheets("s2").Activate
Range("F:I").Sort Key1:=Range("I1"), Order1:=xlDescending
End Sub

Once data in sheet s1 has been updated, run the macro. It will first copy
the data to columns F thru I in sheet s2 and then sort that data by the
values in column I
--
Gary''s Student - gsnu200728


"Gayle C" wrote:

I have a worksheet that updates from another worksheet. I need the second
worksheet to automatically sort in descending order.

Example:
Cols A-B-C-D updates Cols F-G-H-I

A B C D updates F G H
I
name Date Gm Score name date GM Score
Tm 1 1-07 Gm1 200 Tm 1 1-14 Gm2 500
Tm 1 1-07 Gm2 150 Tm 1 1-14 Gm1 300
Tm 1 1-14 GM1 300 Tm 1 1-07 Gm1 200
Tm 1 1-14 GM2 500 Tm 1 1-07 Gm2 150

Once data is updated, I need it to sort in descending order automatically.

Thanks for any assistance in resolving this.

Yes, there can be repeated values, but I don't see that effecting the
outcome. If there were two amounts. that were the same, it would put them
in, let's say position 1 and also in position 2. That would be okay.

Or are you saying if there are repeated values, only one would show up?


"Gary''s Student" wrote:

By any chance, are there repeated values in Column D??

If there are no repeated values, then we can make this work without any
macros !
--
Gary''s Student - gsnu200728


"Gayle C" wrote:

Thanks again! I apologize for the confusion. . I'm very new to Macros. You
are very patient to take the time to work with a novice.

I tried the one you sent but didn't totally understood your terminology.
So when I ran it, I got "run-time error '9', subscript out of range". So I
tried one I'd previously created (which worked but wouldn't auto sort) and
added your info. (my orginal Macro is shown below-gayle2)

From your original explanation, if I understand it, you are moving a,b,c,d
column data from 1 worksheet to f,g,h,i on another sheet. They are actually
on the same sheet. The a.b.c.d data originates from another sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D:D")) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Call gayle2
Application.EnableEvents = True

End Sub
Sub gayle2()
'
' gayle2 Macro
' Macro recorded 6/10/2007 by *
'

'
Range("A11:D218").Select
Selection.Copy
Range("F11").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("I11"), Order1:=xlDescending, Key2:=Range( _
"G11"), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
End Sub

"Gary''s Student" wrote:

Sure. Let's make the assumption that as data is entered or modified in s1,
the last item changed in any row is in column D. This sub will automatically
call sub gayle whenever data is entered into column D or data is changed in
column D:


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D:D")) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Call gayle
Application.EnableEvents = True
End Sub

This is worksheet code, it is very easy to install and automatic to use:

1. from Excel (s1), right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window


--
Gary''s Student - gsnu200728


"Gayle C" wrote:

Thanks, Gary's Student . . I think I got the macro to work, but I have to
click on the button, each time, to get results. I need for it to update
"automatically", each time data is entered, without clicking on the button.

Is there a way to program the macro to run automatically?
Thanks,
Gayle C

"Gary''s Student" wrote:

First enter this macro:

Sub gayle()
Set r1 = Sheets("s1").Range("A:D")
Set r2 = Sheets("s2").Range("F1")
r1.Copy r2
Sheets("s2").Activate
Range("F:I").Sort Key1:=Range("I1"), Order1:=xlDescending
End Sub

Once data in sheet s1 has been updated, run the macro. It will first copy
the data to columns F thru I in sheet s2 and then sort that data by the
values in column I
--
Gary''s Student - gsnu200728


"Gayle C" wrote:

I have a worksheet that updates from another worksheet. I need the second
worksheet to automatically sort in descending order.

Example:
Cols A-B-C-D updates Cols F-G-H-I

A B C D updates F G H
I
name Date Gm Score name date GM Score
Tm 1 1-07 Gm1 200 Tm 1 1-14 Gm2 500
Tm 1 1-07 Gm2 150 Tm 1 1-14 Gm1 300
Tm 1 1-14 GM1 300 Tm 1 1-07 Gm1 200
Tm 1 1-14 GM2 500 Tm 1 1-07 Gm2 150

Once data is updated, I need it to sort in descending order automatically.

Thanks for any assistance in resolving this.

No, its just that if there were no ties, we would need neither sorting nor
macros, but worry not:



Discard all previous macros. Assuming data is entered in other sheets;
sheet €œs1€� will have data in cols A thru D related to linked to the other
sheets. First finish the other sheets and then select sheet €œs1€�. The
following macro will automatically copy cols A thru D to cols F thru I.
Because there may be formulas in cols A thru D, the paste is done with
PasteSpecial. The macro then sorts F thru I by I:

Private Sub Worksheet_Activate()
Application.Calculate
Set r1 = Range("A:D")
Set r2 = Range("F1")
r1.Copy
r2.PasteSpecial Paste:=xlPasteValues
Range("F:I").Sort Key1:=Range("I1"), Order1:=xlDescending
End Sub

Because this is worksheet event code, it must be installed in the correct
place:


Select sheet €œs1€� (or your equivalent sheet)

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you click any other tab and then click back the macro will run.


--
Gary''s Student - gsnu200728


"Gayle C" wrote:

Yes, there can be repeated values, but I don't see that effecting the
outcome. If there were two amounts. that were the same, it would put them
in, let's say position 1 and also in position 2. That would be okay.

Or are you saying if there are repeated values, only one would show up?


"Gary''s Student" wrote:

By any chance, are there repeated values in Column D??

If there are no repeated values, then we can make this work without any
macros !
--
Gary''s Student - gsnu200728


"Gayle C" wrote:

Thanks again! I apologize for the confusion. . I'm very new to Macros. You
are very patient to take the time to work with a novice.

I tried the one you sent but didn't totally understood your terminology.
So when I ran it, I got "run-time error '9', subscript out of range". So I
tried one I'd previously created (which worked but wouldn't auto sort) and
added your info. (my orginal Macro is shown below-gayle2)

From your original explanation, if I understand it, you are moving a,b,c,d
column data from 1 worksheet to f,g,h,i on another sheet. They are actually
on the same sheet. The a.b.c.d data originates from another sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D:D")) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Call gayle2
Application.EnableEvents = True

End Sub
Sub gayle2()
'
' gayle2 Macro
' Macro recorded 6/10/2007 by *
'

'
Range("A11:D218").Select
Selection.Copy
Range("F11").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("I11"), Order1:=xlDescending, Key2:=Range( _
"G11"), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
End Sub

"Gary''s Student" wrote:

Sure. Let's make the assumption that as data is entered or modified in s1,
the last item changed in any row is in column D. This sub will automatically
call sub gayle whenever data is entered into column D or data is changed in
column D:


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D:D")) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Call gayle
Application.EnableEvents = True
End Sub

This is worksheet code, it is very easy to install and automatic to use:

1. from Excel (s1), right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window


--
Gary''s Student - gsnu200728


"Gayle C" wrote:

Thanks, Gary's Student . . I think I got the macro to work, but I have to
click on the button, each time, to get results. I need for it to update
"automatically", each time data is entered, without clicking on the button.

Is there a way to program the macro to run automatically?
Thanks,
Gayle C

"Gary''s Student" wrote:

First enter this macro:

Sub gayle()
Set r1 = Sheets("s1").Range("A:D")
Set r2 = Sheets("s2").Range("F1")
r1.Copy r2
Sheets("s2").Activate
Range("F:I").Sort Key1:=Range("I1"), Order1:=xlDescending
End Sub

Once data in sheet s1 has been updated, run the macro. It will first copy
the data to columns F thru I in sheet s2 and then sort that data by the
values in column I
--
Gary''s Student - gsnu200728


"Gayle C" wrote:

I have a worksheet that updates from another worksheet. I need the second
worksheet to automatically sort in descending order.

Example:
Cols A-B-C-D updates Cols F-G-H-I

A B C D updates F G H
I
name Date Gm Score name date GM Score
Tm 1 1-07 Gm1 200 Tm 1 1-14 Gm2 500
Tm 1 1-07 Gm2 150 Tm 1 1-14 Gm1 300
Tm 1 1-14 GM1 300 Tm 1 1-07 Gm1 200
Tm 1 1-14 GM2 500 Tm 1 1-07 Gm2 150

Once data is updated, I need it to sort in descending order automatically.

Thanks for any assistance in resolving this.

Thanks again!!
It's sorting, but moves the results to row 1 and it needs to be in row 9.
So I made the following changes. . . . but they didn't work. Nothing sorted.

Changed Set r1 to "A9.D9)
changed Set r2 to "F9"
Changed Range ("F9.I9").Sort Key1:=Range("I9"),Order 1:=xlDescending

I'll just keep messing with it . . .and hopefully get it right eventually.

You've been a great help!!!


"Gary''s Student" wrote:

No, its just that if there were no ties, we would need neither sorting nor
macros, but worry not:



Discard all previous macros. Assuming data is entered in other sheets;
sheet €œs1€� will have data in cols A thru D related to linked to the other
sheets. First finish the other sheets and then select sheet €œs1€�. The
following macro will automatically copy cols A thru D to cols F thru I.
Because there may be formulas in cols A thru D, the paste is done with
PasteSpecial. The macro then sorts F thru I by I:

Private Sub Worksheet_Activate()
Application.Calculate
Set r1 = Range("A:D")
Set r2 = Range("F1")
r1.Copy
r2.PasteSpecial Paste:=xlPasteValues
Range("F:I").Sort Key1:=Range("I1"), Order1:=xlDescending
End Sub

Because this is worksheet event code, it must be installed in the correct
place:


Select sheet €œs1€� (or your equivalent sheet)

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you click any other tab and then click back the macro will run.


--
Gary''s Student - gsnu200728


"Gayle C" wrote:

Yes, there can be repeated values, but I don't see that effecting the
outcome. If there were two amounts. that were the same, it would put them
in, let's say position 1 and also in position 2. That would be okay.

Or are you saying if there are repeated values, only one would show up?


"Gary''s Student" wrote:

By any chance, are there repeated values in Column D??

If there are no repeated values, then we can make this work without any
macros !
--
Gary''s Student - gsnu200728


"Gayle C" wrote:

Thanks again! I apologize for the confusion. . I'm very new to Macros. You
are very patient to take the time to work with a novice.

I tried the one you sent but didn't totally understood your terminology.
So when I ran it, I got "run-time error '9', subscript out of range". So I
tried one I'd previously created (which worked but wouldn't auto sort) and
added your info. (my orginal Macro is shown below-gayle2)

From your original explanation, if I understand it, you are moving a,b,c,d
column data from 1 worksheet to f,g,h,i on another sheet. They are actually
on the same sheet. The a.b.c.d data originates from another sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D:D")) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Call gayle2
Application.EnableEvents = True

End Sub
Sub gayle2()
'
' gayle2 Macro
' Macro recorded 6/10/2007 by *
'

'
Range("A11:D218").Select
Selection.Copy
Range("F11").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("I11"), Order1:=xlDescending, Key2:=Range( _
"G11"), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
End Sub

"Gary''s Student" wrote:

Sure. Let's make the assumption that as data is entered or modified in s1,
the last item changed in any row is in column D. This sub will automatically
call sub gayle whenever data is entered into column D or data is changed in
column D:


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D:D")) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Call gayle
Application.EnableEvents = True
End Sub

This is worksheet code, it is very easy to install and automatic to use:

1. from Excel (s1), right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window


--
Gary''s Student - gsnu200728


"Gayle C" wrote:

Thanks, Gary's Student . . I think I got the macro to work, but I have to
click on the button, each time, to get results. I need for it to update
"automatically", each time data is entered, without clicking on the button.

Is there a way to program the macro to run automatically?
Thanks,
Gayle C

"Gary''s Student" wrote:

First enter this macro:

Sub gayle()
Set r1 = Sheets("s1").Range("A:D")
Set r2 = Sheets("s2").Range("F1")
r1.Copy r2
Sheets("s2").Activate
Range("F:I").Sort Key1:=Range("I1"), Order1:=xlDescending
End Sub

Once data in sheet s1 has been updated, run the macro. It will first copy
the data to columns F thru I in sheet s2 and then sort that data by the
values in column I
--
Gary''s Student - gsnu200728


"Gayle C" wrote:

I have a worksheet that updates from another worksheet. I need the second
worksheet to automatically sort in descending order.

Example:
Cols A-B-C-D updates Cols F-G-H-I

A B C D updates F G H
I
name Date Gm Score name date GM Score
Tm 1 1-07 Gm1 200 Tm 1 1-14 Gm2 500
Tm 1 1-07 Gm2 150 Tm 1 1-14 Gm1 300
Tm 1 1-14 GM1 300 Tm 1 1-07 Gm1 200
Tm 1 1-14 GM2 500 Tm 1 1-07 Gm2 150

Once data is updated, I need it to sort in descending order automatically.

Thanks for any assistance in resolving this.

IT WORKS! IT WORKS! I finally got it to work. Thank you so much!!!!
I don't know what people like me would do without people like you.
Gayle


"Gary''s Student" wrote:

No, its just that if there were no ties, we would need neither sorting nor
macros, but worry not:



Discard all previous macros. Assuming data is entered in other sheets;
sheet €œs1€� will have data in cols A thru D related to linked to the other
sheets. First finish the other sheets and then select sheet €œs1€�. The
following macro will automatically copy cols A thru D to cols F thru I.
Because there may be formulas in cols A thru D, the paste is done with
PasteSpecial. The macro then sorts F thru I by I:

Private Sub Worksheet_Activate()
Application.Calculate
Set r1 = Range("A:D")
Set r2 = Range("F1")
r1.Copy
r2.PasteSpecial Paste:=xlPasteValues
Range("F:I").Sort Key1:=Range("I1"), Order1:=xlDescending
End Sub

Because this is worksheet event code, it must be installed in the correct
place:


Select sheet €œs1€� (or your equivalent sheet)

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you click any other tab and then click back the macro will run.


--
Gary''s Student - gsnu200728


"Gayle C" wrote:

Yes, there can be repeated values, but I don't see that effecting the
outcome. If there were two amounts. that were the same, it would put them
in, let's say position 1 and also in position 2. That would be okay.

Or are you saying if there are repeated values, only one would show up?


"Gary''s Student" wrote:

By any chance, are there repeated values in Column D??

If there are no repeated values, then we can make this work without any
macros !
--
Gary''s Student - gsnu200728


"Gayle C" wrote:

Thanks again! I apologize for the confusion. . I'm very new to Macros. You
are very patient to take the time to work with a novice.

I tried the one you sent but didn't totally understood your terminology.
So when I ran it, I got "run-time error '9', subscript out of range". So I
tried one I'd previously created (which worked but wouldn't auto sort) and
added your info. (my orginal Macro is shown below-gayle2)

From your original explanation, if I understand it, you are moving a,b,c,d
column data from 1 worksheet to f,g,h,i on another sheet. They are actually
on the same sheet. The a.b.c.d data originates from another sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D:D")) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Call gayle2
Application.EnableEvents = True

End Sub
Sub gayle2()
'
' gayle2 Macro
' Macro recorded 6/10/2007 by *
'

'
Range("A11:D218").Select
Selection.Copy
Range("F11").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("I11"), Order1:=xlDescending, Key2:=Range( _
"G11"), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
End Sub

"Gary''s Student" wrote:

Sure. Let's make the assumption that as data is entered or modified in s1,
the last item changed in any row is in column D. This sub will automatically
call sub gayle whenever data is entered into column D or data is changed in
column D:


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D:D")) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Call gayle
Application.EnableEvents = True
End Sub

This is worksheet code, it is very easy to install and automatic to use:

1. from Excel (s1), right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window


--
Gary''s Student - gsnu200728


"Gayle C" wrote:

Thanks, Gary's Student . . I think I got the macro to work, but I have to
click on the button, each time, to get results. I need for it to update
"automatically", each time data is entered, without clicking on the button.

Is there a way to program the macro to run automatically?
Thanks,
Gayle C

"Gary''s Student" wrote:

First enter this macro:

Sub gayle()
Set r1 = Sheets("s1").Range("A:D")
Set r2 = Sheets("s2").Range("F1")
r1.Copy r2
Sheets("s2").Activate
Range("F:I").Sort Key1:=Range("I1"), Order1:=xlDescending
End Sub

Once data in sheet s1 has been updated, run the macro. It will first copy
the data to columns F thru I in sheet s2 and then sort that data by the
values in column I
--
Gary''s Student - gsnu200728


"Gayle C" wrote:

I have a worksheet that updates from another worksheet. I need the second
worksheet to automatically sort in descending order.

Example:
Cols A-B-C-D updates Cols F-G-H-I

A B C D updates F G H
I
name Date Gm Score name date GM Score
Tm 1 1-07 Gm1 200 Tm 1 1-14 Gm2 500
Tm 1 1-07 Gm2 150 Tm 1 1-14 Gm1 300
Tm 1 1-14 GM1 300 Tm 1 1-07 Gm1 200
Tm 1 1-14 GM2 500 Tm 1 1-07 Gm2 150

Once data is updated, I need it to sort in descending order automatically.

Thanks for any assistance in resolving this.

You are very welcome.

Thanks for the feedback.
--
Gary''s Student - gsnu200728


"Gayle C" wrote:

IT WORKS! IT WORKS! I finally got it to work. Thank you so much!!!!
I don't know what people like me would do without people like you.
Gayle


"Gary''s Student" wrote:

No, its just that if there were no ties, we would need neither sorting nor
macros, but worry not:



Discard all previous macros. Assuming data is entered in other sheets;
sheet €œs1€� will have data in cols A thru D related to linked to the other
sheets. First finish the other sheets and then select sheet €œs1€�. The
following macro will automatically copy cols A thru D to cols F thru I.
Because there may be formulas in cols A thru D, the paste is done with
PasteSpecial. The macro then sorts F thru I by I:

Private Sub Worksheet_Activate()
Application.Calculate
Set r1 = Range("A:D")
Set r2 = Range("F1")
r1.Copy
r2.PasteSpecial Paste:=xlPasteValues
Range("F:I").Sort Key1:=Range("I1"), Order1:=xlDescending
End Sub

Because this is worksheet event code, it must be installed in the correct
place:


Select sheet €œs1€� (or your equivalent sheet)

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you click any other tab and then click back the macro will run.


--
Gary''s Student - gsnu200728


"Gayle C" wrote:

Yes, there can be repeated values, but I don't see that effecting the
outcome. If there were two amounts. that were the same, it would put them
in, let's say position 1 and also in position 2. That would be okay.

Or are you saying if there are repeated values, only one would show up?


"Gary''s Student" wrote:

By any chance, are there repeated values in Column D??

If there are no repeated values, then we can make this work without any
macros !
--
Gary''s Student - gsnu200728


"Gayle C" wrote:

Thanks again! I apologize for the confusion. . I'm very new to Macros. You
are very patient to take the time to work with a novice.

I tried the one you sent but didn't totally understood your terminology.
So when I ran it, I got "run-time error '9', subscript out of range". So I
tried one I'd previously created (which worked but wouldn't auto sort) and
added your info. (my orginal Macro is shown below-gayle2)

From your original explanation, if I understand it, you are moving a,b,c,d
column data from 1 worksheet to f,g,h,i on another sheet. They are actually
on the same sheet. The a.b.c.d data originates from another sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D:D")) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Call gayle2
Application.EnableEvents = True

End Sub
Sub gayle2()
'
' gayle2 Macro
' Macro recorded 6/10/2007 by *
'

'
Range("A11:D218").Select
Selection.Copy
Range("F11").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("I11"), Order1:=xlDescending, Key2:=Range( _
"G11"), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
End Sub

"Gary''s Student" wrote:

Sure. Let's make the assumption that as data is entered or modified in s1,
the last item changed in any row is in column D. This sub will automatically
call sub gayle whenever data is entered into column D or data is changed in
column D:


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D:D")) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Call gayle
Application.EnableEvents = True
End Sub

This is worksheet code, it is very easy to install and automatic to use:

1. from Excel (s1), right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window


--
Gary''s Student - gsnu200728


"Gayle C" wrote:

Thanks, Gary's Student . . I think I got the macro to work, but I have to
click on the button, each time, to get results. I need for it to update
"automatically", each time data is entered, without clicking on the button.

Is there a way to program the macro to run automatically?
Thanks,
Gayle C

"Gary''s Student" wrote:

First enter this macro:

Sub gayle()
Set r1 = Sheets("s1").Range("A:D")
Set r2 = Sheets("s2").Range("F1")
r1.Copy r2
Sheets("s2").Activate
Range("F:I").Sort Key1:=Range("I1"), Order1:=xlDescending
End Sub

Once data in sheet s1 has been updated, run the macro. It will first copy
the data to columns F thru I in sheet s2 and then sort that data by the
values in column I
--
Gary''s Student - gsnu200728


"Gayle C" wrote:

I have a worksheet that updates from another worksheet. I need the second
worksheet to automatically sort in descending order.

Example:
Cols A-B-C-D updates Cols F-G-H-I

A B C D updates F G H
I
name Date Gm Score name date GM Score
Tm 1 1-07 Gm1 200 Tm 1 1-14 Gm2 500
Tm 1 1-07 Gm2 150 Tm 1 1-14 Gm1 300
Tm 1 1-14 GM1 300 Tm 1 1-07 Gm1 200
Tm 1 1-14 GM2 500 Tm 1 1-07 Gm2 150

Once data is updated, I need it to sort in descending order automatically.

Thanks for any assistance in resolving this.