Loop index confusion

Question

I am attempting to add the values from any twelve consecutive sheets in an external workbook that are in the same cell reference by creating an array that is filled with the values in said cell reference for all sheets in the workbook. Then, using a for loop, I am going to select the twelve most recent values, and add them into a cell on the open workbook. The code below works for this purpose, but I am only trying to fill up twelve cells with summations at a time on the open workbook. Currently, I am filling up thirteen cells instead of twelve cells. I cannot figure out why this is, but it likely has something to do with the code in the for loop or the for loop index.

Code:

Sub LoopValues()
    Application.ScreenUpdating = False  'prevents premature updates during calculation
    
    ' Define vars
    Dim wkbOpen As Excel.Workbook
    Dim wkbRef As Excel.Workbook
 
    Dim ws As Worksheet
    Dim wsName As String
    Dim wsExists As Boolean

    Dim counter As Integer
    Dim i As Integer
    
    ' set workbooks
    Set wkbOpen = ActiveWorkbook
    Set wkbRef = Application.Workbooks.Open("FileName.xlsx")
   
   
   ' creates a string for each sheet in FileName
        ' Formatting: (year, month, day) --> mmyy (disregards day)
        ' Start year: 2020
        ' Start month: Feb
        ' Start mmyy: 0220
    wsName = Format(DateSerial(2020, 2 + counter, 1), "mmyy")
    
    ' Set Counter
    counter = 0
    
    ' condition for do while to run
    wsExists = True
    
    ' declare array for values from sheets
    Dim arr() As Variant
    
    ' create an empty array to store FileName values, and alter in Do While (re dim)
    arr = Array()
    
    Do While wsExists
        
        'create a string for each sheet
         wsName = Format(DateSerial(2020, 2 + counter, 1), "mmyy")
         
        On Error Resume Next
        ' try to set the ws to the name of the current sheet in BatchEmissions
        Set ws = wkbRef.Sheets(wsName)
        
        ' if sheets(wsName) does not exists Err.Number <> 0
        If Err.Number <> 0 Then
            wsExists = False
            On Error GoTo 0
            
        Else
            
            ' resize array (increase bound by one) and ensure previous data is saved
            ReDim Preserve arr(UBound(arr) + 1)
            
            ' update array to inlcude the sheet in FileName's value in AA208
            arr(UBound(arr)) = ws.Range("AA208").Value
        
        End If
            ' increments to consecutive sheet (mmyy) - (counter changes month value)
            counter = counter + 1
    
    Loop
    
    ' close FileName
    wkbRef.Close False
    
    Dim newLowerBound As Integer
    newLowerBound = UBound(arr) - 12
    
    Dim temp As Integer
    
    temp = 0
    
    ' loop through the last twelve values in the array
    For i = newLowerBound To UBound(arr)
    
        
        ' set cell ref to value
        ' to sum arr(0) to arr(11) and increment by i each time
        ' we must transpose the array, and wrap the transposed array inside an index
        ' and for ROWS generate an array each time incremented by i through Evaluate(ROW(1:12) etc
        wkbOpen.Sheets("Site").Cells(99 + temp, "Q").Value = Application.Sum(Application.Index(Application.Transpose(arr), Evaluate("Row(" & (1 + temp) & ":" & (12 + temp) & ")"), 0))

        temp = temp + 1
    
    Next i
    
    Application.ScreenUpdating = True

End Sub

Answer 1

This: newLowerBound = UBound(arr) - 12

to this: newLowerBound = UBound(arr) - 11