auto sort macro
 

I am trying to use a macro to sort a table of data on worksheet 3, whenever I
update a value on worksheet 1 using Excel 2003 (Copy of macro below).
Although the macro picks up the change in data on sheet 1 and runs the sort
subroutine, nothing happens. I have discovered that by moving the tables to
sheet 1 and repointing the macro to the data that it does work and while this
is sufficient, I would like to know why the macro does not work when the
tables are on sheet 3.

Thank you for any advice anyone can provide

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Worksheets(1).Range("B4:U32 "), Target) Is
Nothing) Then
DoSort
End If
End Sub

Private Sub DoSort()
Worksheets(3).Range("A5:I10").Sort Key1:=Worksheets(3).Range("I5"),
Order1:=xlDescending, _
Key2:=Worksheets(3).Range("H5"), Order2:=xlDescending, Header:=xlYes
Worksheets(3).Range("A13:I18").Sort Key1:=Worksheets(3).Range("I13"),
Order1:=xlDescending, _
Key2:=Worksheets(3).Range("H13"), Order2:=xlDescending, Header:=xlYes
Worksheets(3).Range("A21:I27").Sort Key1:=Worksheets(3).Range("I21"),
Order1:=xlDescending, _
Key2:=Worksheets(3).Range("H21"), Order2:=xlDescending, Header:=xlYes
Worksheets(3).Range("A30:I36").Sort Key1:=Worksheets(3).Range("I30"),
Order1:=xlDescending, _
Key2:=Worksheets(3).Range("H30"), Order2:=xlDescending, Header:=xlYes
End Sub

auto sort macro
 

A worksheet_change event, by definition, fires when you do something in a
target cell on the same sheet as the macro. So, IF?? your macro is in sheet
A it will fire when you change sheet A. Also, your macro could be more
efficient using
with sheetA
..do this
end with

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"mpleachy" wrote in message
...
I am trying to use a macro to sort a table of data on worksheet 3, whenever
I
update a value on worksheet 1 using Excel 2003 (Copy of macro below).
Although the macro picks up the change in data on sheet 1 and runs the
sort
subroutine, nothing happens. I have discovered that by moving the tables
to
sheet 1 and repointing the macro to the data that it does work and while
this
is sufficient, I would like to know why the macro does not work when the
tables are on sheet 3.

Thank you for any advice anyone can provide

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Worksheets(1).Range("B4:U32 "), Target) Is
Nothing) Then
DoSort
End If
End Sub

Private Sub DoSort()
Worksheets(3).Range("A5:I10").Sort Key1:=Worksheets(3).Range("I5"),
Order1:=xlDescending, _
Key2:=Worksheets(3).Range("H5"), Order2:=xlDescending,
Header:=xlYes
Worksheets(3).Range("A13:I18").Sort Key1:=Worksheets(3).Range("I13"),
Order1:=xlDescending, _
Key2:=Worksheets(3).Range("H13"), Order2:=xlDescending,
Header:=xlYes
Worksheets(3).Range("A21:I27").Sort Key1:=Worksheets(3).Range("I21"),
Order1:=xlDescending, _
Key2:=Worksheets(3).Range("H21"), Order2:=xlDescending,
Header:=xlYes
Worksheets(3).Range("A30:I36").Sort Key1:=Worksheets(3).Range("I30"),
Order1:=xlDescending, _
Key2:=Worksheets(3).Range("H30"), Order2:=xlDescending,
Header:=xlYes
End Sub