Automatic macro on sheet

[Chris Rees]

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?

 

[Don Guillett]

As ALWAYS, post YOUR code for comments

 

[Chris Rees]

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

 

[Don Guillett]

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