Calling a macro when activating a sheet

[augustyzy]

Hi all!

When I activate a Sheet A, I want to run a macro which does something on another Sheet B, including copy and paste some ranges on sheet B.
The macro runs well independently. However, when I put it in the event of activating Sheet A, the following error occurs:
[h=3][/h]Runtime Error 2147417848(80010108)
Method Copy of Object Range Failed

When debugging, there is also an error: Method 'PasteSpecial' of object 'Range' failed.

Anyone could help me work out this problem please? Thank you very much.

The following is my code:
Private Sub Worksheet_Activate() ' This is Sheet A
Application.Run "Module1.Sort"
End Sub

Sub Sort() ' This is the macro I want to run
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("RankProcessing") ' This is Sheet B

Application.ScreenUpdating = False
Range("RawAll").Copy ' RawAll is a name range in Sheet C
ws.Range("U3").PasteSpecial xlPasteValues
Application.CutCopyMode = False
'ws.Range("A1").Select

Dim xRg As Range
Dim yRg As Range
Set xRg = ws.Range("U3:AG3")
For Each yRg In xRg
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=yRg, Order:=xlAscending
.SetRange ws.Range(yRg, yRg.End(xlDown))
.Header = xlYes
.MatchCase = False
.Apply
End With
Next yRg
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

You need this:

Private Sub Worksheet_Activate() ' This is Sheet A
Application.Run "Sort"
End Sub