VBA - Copy formula to value

[MeesterMarco]

Hi,

I am trying to make a "scoreboard". For that I have a formula in cell AD4 with the sum of a couple of cells (D4:X4) and copied that to 5 other rows. (D5:X5,D6:X6 etc.) Based on that sum the rows must be sorted in Descending order.
When I put the sum as a number in the target-column the sorting of the rows work fine. But when I use the formula there is no sorting. Seems logic to me because there is nothing to sort but a bunch of formulas.

Therefore I want VBA to copy the outcome of the formulas as a VALUE to the sorting column but I didn't manage so far. I tried to make two separate sub-routines and I tried to put them together. Removing the sorting-code doesn't do any good so the problem is probably in the copy-code itself. I'm pretty new to VBA so I probably make a basic mistake. Any help would be very much appreciated…

Here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Column = 26 Then
Range("B3:Z9").Sort Key1:=Range("Z3"), _
Order1:=xlDescending, Header:=xlYes
End If


If Target.Column = 30 Then
Range("AD4:AD9").Select
Selection.Copy
Range("Z4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If


End Sub

 

[MeesterMarco]

I found out myself that the Worksheet_Change event doesn't work with a formula. Solved is differently.