Joinersfolly
04-09-2016, 07:42 AM
The following procedure works as expected, however I want the it happen automatically when the ("F3") cell in each sheet change,s NOTE F3 is a calculated cell.
I want the calculated figure in cell F3 to be sent to column 2 in the ATotalsSheet, where column one in the ATotalsSheet has the same value as the worksheet name from whence the value of F3 comes.
(I tried the opposite approach of sucking the info in to the "ATotalsSheet" using the activate event however the line Sheets("ATotalsSheet").Select causes an infinite loop to occur.)
Sub AttendanceHours()
'This procedure updates the main attendance sheet
'hours for each player when they have been changed in an
'individual players sheet
Dim Answer As String
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> "ACoveringSheet" Then
If ws.Name <> "NewPlayer" Then
If ws.Name <> "ATotalsSheet" Then
ws.Select
Answer = Range("F3")
Dim DeNextRow As Long
Sheets("ATotalsSheet").Select
DeNextRow = Range("A65536").End(xlUp).Row
For I = 1 To DeNextRow
If Cells(I, 1).Value = ws.Name Then
Cells(I, 2) = Answer
End If
Next I
End If
End If
End If
Next ws
End Sub
This is my first post so please advise if I have submitted to the correct forum.
I have been away from VBA for a few years, is VBA and VBAX the same?
Thanks in advance.
I want the calculated figure in cell F3 to be sent to column 2 in the ATotalsSheet, where column one in the ATotalsSheet has the same value as the worksheet name from whence the value of F3 comes.
(I tried the opposite approach of sucking the info in to the "ATotalsSheet" using the activate event however the line Sheets("ATotalsSheet").Select causes an infinite loop to occur.)
Sub AttendanceHours()
'This procedure updates the main attendance sheet
'hours for each player when they have been changed in an
'individual players sheet
Dim Answer As String
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> "ACoveringSheet" Then
If ws.Name <> "NewPlayer" Then
If ws.Name <> "ATotalsSheet" Then
ws.Select
Answer = Range("F3")
Dim DeNextRow As Long
Sheets("ATotalsSheet").Select
DeNextRow = Range("A65536").End(xlUp).Row
For I = 1 To DeNextRow
If Cells(I, 1).Value = ws.Name Then
Cells(I, 2) = Answer
End If
Next I
End If
End If
End If
Next ws
End Sub
This is my first post so please advise if I have submitted to the correct forum.
I have been away from VBA for a few years, is VBA and VBAX the same?
Thanks in advance.