I need a macro to run automatically when a sheet is selected.

However my code needs to get data from another sheet and then paste into the
sheet where the macro automatically runs.

This cause an infinite do loop, how can I avoid this?

As ALWAYS, post YOUR code for comments


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Chris Rees" wrote in message
...
I need a macro to run automatically when a sheet is selected.

However my code needs to get data from another sheet and then paste into
the
sheet where the macro automatically runs.

This cause an infinite do loop, how can I avoid this?

Sorry about that.

Here is the code:
Sub Refresh_main_sheet()
'
' Refresh_main_sheet Macro
' Macro recorded 25/02/2008 by Jonny Protheroe
'

'
Sheets("DATA").Select
Range("B4").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("All AM UK&I Clients").Select
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Application.CutCopyMode = False
Selection.Sort Key1:=Range("B5"), Order1:=xlAscending, Key2:=Range("C5") _
, Order2:=xlDescending, Key3:=Range("E5"), Order3:=xlDescending,
Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal
Range("B2:H2").Select
End Sub

Right click sheet tabview codecopy/paste this
Private Sub Worksheet_Activate()

Set mysht = Sheets("Data")
With mysht
.Range(.Range("b4"), .Range("b4").End(xlToRight).End(xlDown)).Copy
ActiveSheet.Range("b4").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End With

With Range(Range("b4"), Range("b4").End(xlToRight).End(xlDown))
.Replace what:="0", Replacement:="", LookAt:=xlWhole
.Sort Key1:=.Range("B5"), Order1:=xlAscending, Key2:=.Range("C5") _
, Order2:=xlDescending, Key3:=.Range("E5"), Order3:=xlDescending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Chris Rees" wrote in message
...
Sorry about that.

Here is the code:
Sub Refresh_main_sheet()
'
' Refresh_main_sheet Macro
' Macro recorded 25/02/2008 by Jonny Protheroe
'

'
Sheets("DATA").Select
Range("B4").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("All AM UK&I Clients").Select
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Application.CutCopyMode = False
Selection.Sort Key1:=Range("B5"), Order1:=xlAscending,
Key2:=Range("C5") _
, Order2:=xlDescending, Key3:=Range("E5"), Order3:=xlDescending,
Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal
Range("B2:H2").Select
End Sub