Sort formula not working
 

I have over 1,200 worksheets with this formula:
Private Sub Worksheet_Activate()
Application.Calculate
Set r1 = Range("a10:E218")
Set r2 = Range("g10")
r1.Copy
r2.PasteSpecial Paste:=xlPasteValues
Range ("G10:K218") .Sort Key1:=Range ("K10") , Order1:=xlDescending
End Sub
Which does what I want when I "Open" the worksheet. I "thought" when I got
to my final page & did a link to each of the worksheets the link would
activate the sort on each worksheet to get my data for the final sheet. This
of course is not happening. Can someone tell me how to change this so it
will automatically sort as the data changes on another linked sheet that
feeds the sheet with the data that needs to be sorted. I have months of work
in this project & the "sorted" data is a must. Or any suggestions. Also I
have on 1 worksheet several of the above formulas to sort different linked
data files.