I have 2 worksheets in the same workbook called "DATA" and "Scores".

Structure of sheet "Scores" is as follows

A B C
1 Player Caps goals
2 Beckham 65
3 Shearer 67
4 Beattie 2




I need to populate column C with number of goals scored by each player
(Column A) from worksheet "DATA". The code below runs fine when I select the
player (Range A) to become the ActiveCell and then run the code and only if
the code and the three column are in the same worksheet, which is "sheet
"DATA".


I need to add a loop to the code to run it from sheet "scores". Of course
the line
mScorer = ActiveCell
needs to be changed to reflect going through all the players





Public Sub SumGoals()

Dim mScorer As String
Dim mTotal As Integer
Dim mGoals As Integer
Dim myCell As Range

mTotal = 0
mScorer = ActiveCell

With Worksheets("Data").Range("AD3:AL500")

For Each c In Range("AD2:AL500")

If c.Value = mScorer Then
firstAddress = c.Address
mGoals = c.Offset(0, 1).Value
mTotal = mTotal + mGoals
End If
Next c
End With

If mTotal 0 Then
ActiveCell.Offset(0, 3).Value = mTotal
End If


End Sub