Option Explicit
Sub UpdateReportingPercentage()
Dim curYear As Integer
Dim curMonth As Integer
Dim curCell As Range
Dim curRow As Integer
Dim curCol As Integer
Dim Employees As Worksheet
Dim Reporting As Worksheet
Set Employees = ThisWorkbook.Sheets("Employees_trained")
Set Reporting = ThisWorkbook.Sheets("Reporting")
Application.ScreenUpdating = False
curYear = year(Now())
curMonth = month(Now())
curRow = 10
curCol = 2
Employees.Activate
Set curCell = Cells(curRow, curCol)
While curCell.Value <> curYear
curCol = curCol + 1
Set curCell = Cells(curRow, curCol)
Wend
curRow = curRow + 1
Set curCell = Cells(curRow, curCol)
While curCell.Value <> curMonth
curCol = curCol + 1
Set curCell = Cells(curRow, curCol)
Wend
With Reporting
.Unprotect
AuthorizedTrained curRow, curCol, Reporting
AffectedTrained curRow, curCol, Reporting
.Protect
End With
Reporting.Activate
Application.ScreenUpdating = True
End Sub
Sub AuthorizedTrained(ByVal curRow As Integer, ByVal curCol As Integer, ByRef Reporting As Worksheet)
Dim authorized As Double
Dim curCell As Range
Set curCell = Cells(curRow + 5, curCol)
With Reporting
authorized = curCell.Value / Cells(16, 32).Value
.Cells(14, 42) = authorized
End With
End Sub
Sub AffectedTrained(ByVal curRow As Integer, curCol As Integer, ByRef Reporting As Worksheet)
Dim affected As Double
Dim curCell As Range
Set curCell = Cells(curRow + 6, curCol)
With Reporting
affected = curCell.Value / Cells(17, 32).Value
.Cells(15, 42) = affected
End With
End Sub