carcharoth2554
New Member
- Joined
- Feb 5, 2013
- Messages
- 30
Hi guys,
Excel 2010 on Win 7 64bit
Below is my code to date that in short outlines the behaviours of my reporting function. This code is run from a master workbook that references tables of data contained within several other open workbooks that are hidden in the background. Workbooks represent Projects (currently 11 workbooks), worksheets represent Sub-Projects (currently 68 worksheets spread across the 11 workbooks) and each worksheet contains one table of data averaging around 20 x 146 cells in size. All table data is number format with 1 decimal place but there are many empty cells.
The reporting function finds where data is present on the table by looping through all cells, runs through some IF statements to define the correct behaviours and then sends the totals back to the master report sheet.
The code actually functioned perfectly, doing exactly what I wanted it to until I loaded a simple 13 x 11 array of wages (which I did to try to refine the code) which it then used in some cases to calculate an hourly cost. Once I did this, the processing time for the reporting function jumped from around 12 seconds to around 42 seconds. It still does exactly what I want; I just get worried about crashing especially if I added more data.
As you can see from the code yourself, there is a great deal of unnecessary & clumsy code remaining so I would love some help so that first I can cut some of this down, and second, bring that processing time as far down as possible.
As I said, I think this is an advanced one so would be grateful for a veteran to take a gander.
Thanks guys, I know you will come through for me!
Excel 2010 on Win 7 64bit
Below is my code to date that in short outlines the behaviours of my reporting function. This code is run from a master workbook that references tables of data contained within several other open workbooks that are hidden in the background. Workbooks represent Projects (currently 11 workbooks), worksheets represent Sub-Projects (currently 68 worksheets spread across the 11 workbooks) and each worksheet contains one table of data averaging around 20 x 146 cells in size. All table data is number format with 1 decimal place but there are many empty cells.
The reporting function finds where data is present on the table by looping through all cells, runs through some IF statements to define the correct behaviours and then sends the totals back to the master report sheet.
The code actually functioned perfectly, doing exactly what I wanted it to until I loaded a simple 13 x 11 array of wages (which I did to try to refine the code) which it then used in some cases to calculate an hourly cost. Once I did this, the processing time for the reporting function jumped from around 12 seconds to around 42 seconds. It still does exactly what I want; I just get worried about crashing especially if I added more data.
As you can see from the code yourself, there is a great deal of unnecessary & clumsy code remaining so I would love some help so that first I can cut some of this down, and second, bring that processing time as far down as possible.
As I said, I think this is an advanced one so would be grateful for a veteran to take a gander.
Thanks guys, I know you will come through for me!
Code:
Function dynamicArray(lookupValue As String, lookupDate As Long, table As Range)
' Define the row starting cell, Row n maps to the first row where the range is found in the spreadsheet.
' Row 1 is the first row and 1,048,576 is the last possible row.
Dim rowStart As Integer
Dim rowSize As Integer
' Define the column starting cell, Column A maps to 1, Column B maps to 2, et cetera.
' Column A is the first column and XFD is the last possible column.
Dim columnStart As Integer
Dim columnSize As Integer
' Create a dynamic multiple dimension array without any physical size.
Dim multidimensionArray As Variant
' Define and declare a local returnValue variable.
Dim returnValue As Double
'returnValue = False
' Assign the starting row and column values, and the length of values.
' Since you need to add the row and column to the starting grid coordinates, you need to use 0-based numbering,
' which means you subtract one from the length.
rowStart = table.Row
rowSize = table.Rows.Count - 1
columnStart = table.Column
columnSize = table.Columns.Count - 1
' This demonstrates that the range starts in the row and column, and
' the length and width of the multiple dimension array.
' ----------------------------------------------------------------------
' Insert single quotes for the next two lines to suppress testing the program with variables.
'MsgBox ("(RowStart [" + CStr(rowStart) + "], (ColStart [" + CStr(columnStart) + "]) " + _
'"(RowSize [" + CStr(rowSize) + "] ColSize [" + CStr(columnSize) + "])")
' ----------------------------------------------------------------------
' Redimension the arrays maximum size, rows first, columns second.
ReDim multidimensionArray(rowSize, columnSize)
' Read through the range and assign it to a local and dynamically sized array variable.
' An important note to those unfamilar with the Cells function, it works on the active worksheet and uses two
' parameters, the absolute row and column number or relative row and column numbers. In this sample, the
' easiest solution is to use absolute row an dolumn numbers.
For i = 0 To rowSize
For j = 0 To columnSize
multidimensionArray(i, j) = CStr(Workbooks("PD0M - Master Tracker").Worksheets("Resources").Cells(rowStart + i, columnStart + j))
Next j
Next i
' Read through the local variable range and view the content set.
For i = 0 To rowSize
For j = 0 To columnSize
' Check if the lookupValue has been found and return true.
If lookupValue = multidimensionArray(i, j) Then
If lookupDate = "2012" Then
returnValue = multidimensionArray(i, j + 4)
ElseIf lookupDate = "2013" Then
returnValue = multidimensionArray(i, j + 6)
ElseIf lookupDate = "2014" Then
returnValue = multidimensionArray(i, j + 8)
ElseIf lookupDate = "2015" Then
returnValue = multidimensionArray(i, j + 10)
End If
Exit For
End If
Next j
Next i
' Return a Boolean value: true when found and false when not found.
dynamicArray = returnValue
End Function
Private Sub CommandButton1_Click()
'Define variables
Dim sdsheet As Worksheet, ersheet As Worksheet
'Local Variables
Dim trackerAddress As String
Dim wks As Worksheet
Dim strSubAddress As String
Dim strDisplayText As String
Dim resourceSheet As Worksheet
Dim resourceDest As Worksheet
Dim delDescrip As String
Dim profitTotal As Double
Dim reportYear As Long
Dim wageYear As Long
Dim week As Integer
Dim taskCode, month As String
Dim name As String
Dim wageReturn As Double
Dim u As Long, c As Long
Dim a
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set sdsheet = ThisWorkbook.Sheets("Summary")
Set ersheet = ThisWorkbook.Sheets("Report Totals")
ersheet.Range("A1:J33").ClearContents
ersheet.Range("A1:J1") = Array("Project", "Total Budget (Hours)", "Total Worked (Hours)", "Total Remainder (Hours)", "Percentage of Budgeted Hours Used (%)", "Total Budget (£)", "Total Spend (£)", "Total Remainder (£)", "Percentage of Budget Used (%)", "Percentage of Deliverable Complete (%)")
If sdsheet.Cells(Rows.Count, 1).End(xlUp).Row = 1 Then
sdLR = 2
Else
sdLR = sdsheet.Cells(Rows.Count, 1).End(xlUp).Row
End If
Y = 2 'starting row
For X = 2 To sdLR
'put on the ersheet
ersheet.Cells(Y, 1) = sdsheet.Cells(X, 2)
ersheet.Cells(Y, 2) = sdsheet.Cells(X, 3)
ersheet.Cells(Y, 3) = sdsheet.Cells(X, 4)
ersheet.Cells(Y, 4) = sdsheet.Cells(X, 5)
ersheet.Cells(Y, 5) = sdsheet.Cells(X, 6)
ersheet.Cells(Y, 6) = sdsheet.Cells(X, 7)
ersheet.Cells(Y, 7) = sdsheet.Cells(X, 8)
ersheet.Cells(Y, 8) = sdsheet.Cells(X, 9)
ersheet.Cells(Y, 9) = sdsheet.Cells(X, 10)
ersheet.Cells(Y, 10) = sdsheet.Cells(X, 11)
Y = Y + 1
Next X
ersheet.Range("A2") = "Totals:"
'Define static parameters
reportYear = Workbooks("PD0M - Master Tracker").Worksheets("Report Totals").Range("F66").Value
month = Workbooks("PD0M - Master Tracker").Worksheets("Report Totals").Range("E66").Value
week = Workbooks("PD0M - Master Tracker").Worksheets("Report Totals").Range("D66").Value - 2
For i = 3 To 99
If Workbooks("PD0M - Master Tracker").Worksheets("Summary").Cells(i, 1) <> "" Then
'Define workbook needed and activate
trackerAddress = Workbooks("PD0M - Master Tracker").Worksheets("Summary").Cells(i, 1).Value
With Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets("Summary")
.Range("D68:K96").ClearContents
.Range("D68:K96").Interior.ColorIndex = 24
'Resource cost calculating method
For Each wks In Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets
strDisplayText = wks.name
Set resourceDest = Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText)
If strDisplayText <> "Summary" Then
If strDisplayText <> "Resources" Then
z = 9
Do While Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(6, z) <> ""
profitTotal = 0
X = 11
For X = 11 To 146
taskCode = ""
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z) <> "" Then
taskCode = Right(Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(7, z), 2)
name = Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(6, z)
wageYear = Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3)
wageReturn = dynamicArray(name, wageYear, Workbooks("PD0M - Master Tracker").Worksheets("Resources").Range("A13:K50"))
If taskCode = "NC" Then
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
NC2 = NC2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
NC7 = NC7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
NC3 = NC3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
NC6 = NC6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
NC4 = NC4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
NC5 = NC5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
'totals
NC1 = NC1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
NC8 = NC8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
ElseIf taskCode = "TR" Then
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
TR2 = TR2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
TR7 = TR7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
TR3 = TR3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
TR6 = TR6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
TR4 = TR4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
TR5 = TR5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
TR1 = TR1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
TR8 = TR8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
ElseIf taskCode = "MD" Then
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
MD2 = MD2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
MD7 = MD7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
MD3 = MD3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
MD6 = MD6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
MD4 = MD4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
MD5 = MD5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
MD1 = MD1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
MD8 = MD8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
ElseIf taskCode = "FA" Then
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
FA2 = FA2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
FA7 = FA7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
FA3 = FA3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
FA6 = FA6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
FA4 = FA4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
FA5 = FA5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
FA1 = FA1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
FA8 = FA8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
ElseIf taskCode = "CP" Then
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
CP2 = CP2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
CP7 = CP7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
CP3 = CP3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
CP6 = CP6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
CP4 = CP4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
CP5 = CP5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
CP1 = CP1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
CP8 = CP8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
ElseIf taskCode = "RW" Then
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
RW2 = RW2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
RW7 = RW7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
RW3 = RW3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
RW6 = RW6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
RW4 = RW4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
RW5 = RW5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
RW1 = RW1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
RW8 = RW8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
ElseIf taskCode = "RE" Then
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
RE2 = RE2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
RE7 = RE7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
RE3 = RE3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
RE6 = RE6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
RE4 = RE4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
RE5 = RE5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
RE1 = RE1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
RE8 = RE8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
ElseIf taskCode = "SC" Then
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
SC2 = SC2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
SC7 = SC7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
SC3 = SC3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
SC6 = SC6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
SC4 = SC4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
SC5 = SC5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
SC1 = SC1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
SC8 = SC8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
ElseIf taskCode = "CA" Then
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
CA2 = CA2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
CA7 = CA7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
CA3 = CA3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
CA6 = CA6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
CA4 = CA4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
CA5 = CA5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
CA1 = CA1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
CA8 = CA8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
ElseIf taskCode = "PM" Then
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
PM2 = PM2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
PM7 = PM7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
PM3 = PM3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
PM6 = PM6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
PM4 = PM4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
PM5 = PM5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
PM1 = PM1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
PM8 = PM8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
ElseIf taskCode = "AL" Then
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
AL2 = AL2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
AL7 = AL7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
AL3 = AL3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
AL6 = AL6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
AL4 = AL4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
AL5 = AL5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
AL1 = AL1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
AL8 = AL8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
ElseIf taskCode = "LL" Then
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
LL2 = LL2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
LL7 = LL7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
LL3 = LL3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
LL6 = LL6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
LL4 = LL4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
LL5 = LL5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
LL1 = LL1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
LL8 = LL8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
ElseIf taskCode = "SL" Then
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
SL2 = SL2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
SL7 = SL7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
SL3 = SL3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
SL6 = SL6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
SL4 = SL4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
SL5 = SL5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
SL1 = SL1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
SL8 = SL8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
ElseIf taskCode = "MP" Then
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
MP2 = MP2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
MP7 = MP7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
MP3 = MP3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
MP6 = MP6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
MP4 = MP4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
MP5 = MP5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
MP1 = MP1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
MP8 = MP8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
ElseIf taskCode = "MG" Then
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
MG2 = MG2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
MG7 = MG7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
MG3 = MG3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
MG6 = MG6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
MG4 = MG4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
MG5 = MG5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
MG1 = MG1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
MG8 = MG8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
ElseIf taskCode = "TB" Then
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
TB2 = TB2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
TB7 = TB7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
TB3 = TB3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
TB6 = TB6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
TB4 = TB4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
TB5 = TB5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
TB1 = TB1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
TB8 = TB8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
ElseIf taskCode = "ST" Then
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
ST2 = ST2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
ST7 = ST7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
ST3 = ST3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
ST6 = ST6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
ST4 = ST4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
ST5 = ST5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
ST1 = ST1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
ST8 = ST8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
ElseIf taskCode = "PT" Then
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
PT2 = PT2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
PT7 = PT7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
PT3 = PT3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
PT6 = PT6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
PT4 = PT4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
PT5 = PT5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
PT1 = PT1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
PT8 = PT8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
ElseIf taskCode = "TT" Then
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
TT2 = TT2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
TT7 = TT7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
TT3 = TT3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
TT6 = TT6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
TT4 = TT4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
TT5 = TT5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
TT1 = TT1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
TT8 = TT8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
ElseIf taskCode = "WT" Then
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
WT2 = WT2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
WT7 = WT7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
WT3 = WT3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
WT6 = WT6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
WT4 = WT4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
WT5 = WT5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
WT1 = WT1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
WT8 = WT8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
ElseIf taskCode = "AP" Then
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
AP2 = AP2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
AP7 = AP7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
AP3 = AP3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
AP6 = AP6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
AP4 = AP4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
AP5 = AP5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
AP1 = AP1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
AP8 = AP8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
ElseIf taskCode = "AG" Then
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
AG2 = AG2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
AG7 = AG7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
AG3 = AG3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
AG6 = AG6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
AG4 = AG4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
AG5 = AG5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
AG1 = AG1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
AG8 = AG8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
End If
End If
Next
z = z + 1
Loop
End If
End If
Next wks
End With
End If
Next i
With Workbooks("PD0M - Master Tracker").Worksheets("Report Totals")
'######################################## Array
.Cells(69, 4) = NC4
.Cells(69, 6) = NC3
.Cells(69, 8) = NC2
.Cells(69, 10) = NC1
.Cells(69, 11) = NC8
.Cells(69, 9) = NC7
.Cells(69, 7) = NC6
.Cells(69, 5) = NC5
.Cells(70, 4) = TR4
.Cells(70, 6) = TR3
.Cells(70, 8) = TR2
.Cells(70, 10) = TR1
.Cells(70, 11) = TR8
.Cells(70, 9) = TR7
.Cells(70, 7) = TR6
.Cells(70, 5) = TR5
.Cells(71, 4) = MD4
.Cells(71, 6) = MD3
.Cells(71, 8) = MD2
.Cells(71, 10) = MD1
.Cells(71, 11) = MD8
.Cells(71, 9) = MD7
.Cells(71, 7) = MD6
.Cells(71, 5) = MD5
.Cells(72, 4) = FA4
.Cells(72, 6) = FA3
.Cells(72, 8) = FA2
.Cells(72, 10) = FA1
.Cells(72, 11) = FA8
.Cells(72, 9) = FA7
.Cells(72, 7) = FA6
.Cells(72, 5) = FA5
.Cells(73, 4) = CP4
.Cells(73, 6) = CP3
.Cells(73, 8) = CP2
.Cells(73, 10) = CP1
.Cells(73, 11) = CP8
.Cells(73, 9) = CP7
.Cells(73, 7) = CP6
.Cells(73, 5) = CP5
.Cells(74, 4) = RW4
.Cells(74, 6) = RW3
.Cells(74, 8) = RW2
.Cells(74, 10) = RW1
.Cells(74, 11) = RW8
.Cells(74, 9) = RW7
.Cells(74, 7) = RW6
.Cells(74, 5) = RW5
.Cells(75, 4) = RE4
.Cells(75, 6) = RE3
.Cells(75, 8) = RE2
.Cells(75, 10) = RE1
.Cells(75, 11) = RE8
.Cells(75, 9) = RE7
.Cells(75, 7) = RE6
.Cells(75, 5) = RE5
.Cells(76, 4) = SC4
.Cells(76, 6) = SC3
.Cells(76, 8) = SC2
.Cells(76, 10) = SC1
.Cells(76, 11) = SC8
.Cells(76, 9) = SC7
.Cells(76, 7) = SC6
.Cells(76, 5) = SC5
.Cells(77, 4) = CA4
.Cells(77, 6) = CA3
.Cells(77, 8) = CA2
.Cells(77, 10) = CA1
.Cells(77, 11) = CA8
.Cells(77, 9) = CA7
.Cells(77, 7) = CA6
.Cells(77, 5) = CA5
.Cells(78, 4) = PM4
.Cells(78, 6) = PM3
.Cells(78, 8) = PM2
.Cells(78, 10) = PM1
.Cells(78, 11) = PM8
.Cells(78, 9) = PM7
.Cells(78, 7) = PM6
.Cells(78, 5) = PM5
.Cells(79, 4) = AL4
.Cells(79, 6) = AL3
.Cells(79, 8) = AL2
.Cells(79, 10) = AL1
.Cells(79, 11) = AL8
.Cells(79, 9) = AL7
.Cells(79, 7) = AL6
.Cells(79, 5) = AL5
.Cells(80, 4) = LL4
.Cells(80, 6) = LL3
.Cells(80, 8) = LL2
.Cells(80, 10) = LL1
.Cells(80, 11) = LL8
.Cells(80, 9) = LL7
.Cells(80, 7) = LL6
.Cells(80, 5) = LL5
.Cells(81, 4) = SL4
.Cells(81, 6) = SL3
.Cells(81, 8) = SL2
.Cells(81, 10) = SL1
.Cells(81, 11) = SL8
.Cells(81, 9) = SL7
.Cells(81, 7) = SL6
.Cells(81, 5) = SL5
.Cells(82, 4) = MP4
.Cells(82, 6) = MP3
.Cells(82, 8) = MP2
.Cells(82, 10) = MP1
.Cells(82, 11) = MP8
.Cells(82, 9) = MP7
.Cells(82, 7) = MP6
.Cells(82, 5) = MP5
.Cells(83, 4) = MG4
.Cells(83, 6) = MG3
.Cells(83, 8) = MG2
.Cells(83, 10) = MG1
.Cells(83, 11) = MG8
.Cells(83, 9) = MG7
.Cells(83, 7) = MG6
.Cells(83, 5) = MG5
.Cells(84, 4) = TB4
.Cells(84, 6) = TB3
.Cells(84, 8) = TB2
.Cells(84, 10) = TB1
.Cells(84, 11) = TB8
.Cells(84, 9) = TB7
.Cells(84, 7) = TB6
.Cells(84, 5) = TB5
.Cells(85, 4) = ST4
.Cells(85, 6) = ST3
.Cells(85, 8) = ST2
.Cells(85, 10) = ST1
.Cells(85, 11) = ST8
.Cells(85, 9) = ST7
.Cells(85, 7) = ST6
.Cells(85, 5) = ST5
.Cells(86, 4) = PT4
.Cells(86, 6) = PT3
.Cells(86, 8) = PT2
.Cells(86, 10) = PT1
.Cells(86, 11) = PT8
.Cells(86, 9) = PT7
.Cells(86, 7) = PT6
.Cells(86, 5) = PT5
.Cells(87, 4) = TT4
.Cells(87, 6) = TT3
.Cells(87, 8) = TT2
.Cells(87, 10) = TT1
.Cells(87, 11) = TT8
.Cells(87, 9) = TT7
.Cells(87, 7) = TT6
.Cells(87, 5) = TT5
.Cells(88, 4) = WT4
.Cells(88, 6) = WT3
.Cells(88, 8) = WT2
.Cells(88, 10) = WT1
.Cells(88, 11) = WT8
.Cells(88, 9) = WT7
.Cells(88, 7) = WT6
.Cells(88, 5) = WT5
.Cells(89, 4) = AP4
.Cells(89, 6) = AP3
.Cells(89, 8) = AP2
.Cells(89, 10) = AP1
.Cells(89, 11) = AP8
.Cells(89, 9) = AP7
.Cells(89, 7) = AP6
.Cells(89, 5) = AP5
.Cells(90, 4) = AG4
.Cells(90, 6) = AG3
.Cells(90, 8) = AG2
.Cells(90, 10) = AG1
.Cells(90, 11) = AG8
.Cells(90, 9) = AG7
.Cells(90, 7) = AG6
.Cells(90, 5) = AG5
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
