Automatic macro on sheet

  • Thread starter Thread starter Chris Rees
  • Start date Start date
C

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?
 
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
 
Back
Top