VBA Sort one sheet display another
 

sheet1 has first names and last names data on it.
i have three sort macros which all are working for sheet1.

sheet2 sheet3 and sheet4 have the linked data from sheet1.

here is what i want to do. When a user clicks on sheet tab 2, i would
like the data on sheet1 sorted with sort1. and after the sort display
sheet2.

if the user clicks on sheet3 i want the data on sheet1 sorted with sort2.
and after the sort display sheet2. etc.....

when I do this

Private Sub Worksheet_Activate()
sort1
Worksheets("sheet2").Activate
End Sub

well that gives me and endless loop of sorting of sheet1.

i thought there was a way around this but I can not remember how to do
it.

anyone know the work around for this?

VBA Sort one sheet display another
 

try putting your sort1 and sort2 macros in a regular module and have the
sheet activate

sort1
sort2



--
Don Guillett
SalesAid Software

"Pete" wrote in message
...
sheet1 has first names and last names data on it.
i have three sort macros which all are working for sheet1.

sheet2 sheet3 and sheet4 have the linked data from sheet1.

here is what i want to do. When a user clicks on sheet tab 2, i would
like the data on sheet1 sorted with sort1. and after the sort display
sheet2.

if the user clicks on sheet3 i want the data on sheet1 sorted with sort2.
and after the sort display sheet2. etc.....

when I do this

Private Sub Worksheet_Activate()
sort1
Worksheets("sheet2").Activate
End Sub

well that gives me and endless loop of sorting of sheet1.

i thought there was a way around this but I can not remember how to do
it.

anyone know the work around for this?

VBA Sort one sheet display another
 

Assume your sort macros are activating sheet1 (which isn't necessary and
eliminating that approach would be the best solution, but in the interim).

Private Sub Worksheet_Activate()
On Error goto ErrHandler
Application.EnableEvents = False
sort1
Worksheets("sheet2").Activate
ErrHandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy

"Pete" wrote in message
...
sheet1 has first names and last names data on it.
i have three sort macros which all are working for sheet1.

sheet2 sheet3 and sheet4 have the linked data from sheet1.

here is what i want to do. When a user clicks on sheet tab 2, i would
like the data on sheet1 sorted with sort1. and after the sort display
sheet2.

if the user clicks on sheet3 i want the data on sheet1 sorted with sort2.
and after the sort display sheet2. etc.....

when I do this

Private Sub Worksheet_Activate()
sort1
Worksheets("sheet2").Activate
End Sub

well that gives me and endless loop of sorting of sheet1.

i thought there was a way around this but I can not remember how to do
it.

anyone know the work around for this?

VBA Sort one sheet display another
 

I'm guessing your sort routines include an .activate or .select.

Maybe you could rewrite them so that you don't have to activate/select.

I put this code behind sheet2:

Option Explicit
Private Sub Worksheet_Activate()
With Worksheets("sheet1")
.Range("a1:g23").Sort key1:=.Range("a1"), _
order1:=xlAscending, header:=xlNo
End With
End Sub

And it worked ok.

An ugly alternative:

Add some code that disables events--so excel stops looking for triggers to
events.

application.enableevents = false
'do as much as you want
application.enableevents = true

If I were doing this, I'd try to use the first technique. I think it makes the
code easier to read/update later--and if something goes wrong, you don't have to
worry about some way of turning the .enableevents back on.

Pete wrote:

sheet1 has first names and last names data on it.
i have three sort macros which all are working for sheet1.

sheet2 sheet3 and sheet4 have the linked data from sheet1.

here is what i want to do. When a user clicks on sheet tab 2, i would
like the data on sheet1 sorted with sort1. and after the sort display
sheet2.

if the user clicks on sheet3 i want the data on sheet1 sorted with sort2.
and after the sort display sheet2. etc.....

when I do this

Private Sub Worksheet_Activate()
sort1
Worksheets("sheet2").Activate
End Sub

well that gives me and endless loop of sorting of sheet1.

i thought there was a way around this but I can not remember how to do
it.

anyone know the work around for this?

--

Dave Peterson