Run code from one worksheet to populate another.

[Majeed]

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

 

[losmac]

Public Sub SumGoals()
Dim cellRow As String, firstAddress As String
Dim mScorer As String
Dim mTotal As Integer
Dim mGoals As Integer
Dim myCell As Range
Dim c As Object

mTotal = 0
cellRow = ActiveCell.Row
mScorer = ActiveSheet.Range("A" & cellRow)

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
ActiveSheet.Range("A" & cellRow).Offset(0, 3).Value =
mTotal
End If


End Sub

 

[Majeed]

Thanks losmac , but this code does not loop through worksheet "Scores" and
somehow does not run the code to calculate the number of goal from worksheet
"Data"