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
(E-Mail Removed)
"Chris Rees" <(E-Mail Removed)> wrote in message
news:4B3DC0BB-AA49-4F2D-9088-(E-Mail Removed)...
>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 tab>view code>copy/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
(E-Mail Removed)
"Chris Rees" <(E-Mail Removed)> wrote in message
news:0289A08C-EB72-4981-9CF3-(E-Mail Removed)...
> 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