Changing formula in cell using for loop index numbers as cell ref

[Dax4201]

I can't find anything that seems address my problem. I am trying to build formulas in a sheet referencing cells in other sheets in a workbook. I won't know the number of sheets in the workbook until run-time. As I am building those sheets based the number of sheet in a separate workbook.

I am having trouble selecting the cell to put the formula into. Range uses "A1" reference, but I need to use the indexes from my For loops to reference the cell.

As in the code below. Any suggestions would be greatly appreciated.

Private Sub create_Dist_Sheet()
Dim i As Integer
Dim j As Integer
Dim tempPath As String

    'template sheet 
    tempPath = "C:\Documents and Settings...."
    Set newSheet = newBook.Sheets.Add(Type:=tempPath)
    
    For i = 4 To 26 Step 2
        For j = 3 To 65
            
            With newSheet
               .Cells(j, i).FormulaR1C1 = "=SUM(" & newBook.Sheet(2).Name & ":" & _
                newBook.Sheets(newBook.Sheets.Count).Name & "!" & Chr(i + 64) & ")"
            End With
        Next j
          
     Next i
End Sub

 

[Dax4201]

Found it. I spelled Cells as Cell. Thanks