Dynamic Sum Range Addresses...(?)

[Kenny_D]

I'm not really sure what you'd call what I'm trying to do, but here's my best attempt to describe it.
I'm creating a summary sheet that will pull from an unknown number of sheets. I've employed Indirect to assist me as I hate #REF errors should referenced sheets get deleted. I have my macro first get the names of any relevant sheet, then build a summary "table" per sheet on the Summary Sheet. All this works like a charm. Now, I'd like to add a total line at the end that sums only select items from each little table. My question: How can I capture the cell address of these items so I can use them in a sum formula? I want it to show up as a "=Sum(B3,B11,B19,...B)" formula so others can see what is being summed up and follow it better.
Below is my total code. I haven't yet attempted to add the total line, as I don't really know where to start. Also, if you also see some other suggestions to improve upon, please let me know.

Sub ListSheetNamesInNewWorkbook()
    Dim objNewWorkbook As Workbook
    Dim objNewWorksheet As Worksheet

    w = 0
    'Set objNewWorkbook = Excel.Application.Workbooks.Add
    Set objNewWorksheet = ThisWorkbook.Sheets("Sheet3")

    For i = 8 To ThisWorkbook.Sheets.Count 'my workbook has 7 sheets before the standardized sheets I need for summation
        objNewWorksheet.Cells(i - 6, 1) = i
        objNewWorksheet.Cells(i - 6, 2) = ThisWorkbook.Sheets(i).Name
     
      With Sheet1
       .Cells(1 + 8 * w, 1) = ThisWorkbook.Sheets(i).Name 
       .Cells(1 + 8 * w, 1).Font.Bold = True
       .Cells(1 + 8 * w, 1).Font.Underline = xlUnderlineStyleSingle
       .Cells(1 + 8 * w, 1).HorizontalAlignment = xlCenter
       .Cells(1 + 8 * w, 1).Interior.ColorIndex = 44
       .Cells(2 + 8 * w, 1) = "First line description"
       .Cells(2 + 8 * w, 2).FormulaR1C1 = "=IF(R[-1]C[-1]="""","""",INDIRECT(""'""&R[-1]C[-1]&""'!T57""))"  ' all these reference locations of standardized sheets
       .Cells(3 + 8 * w, 1).FormulaR1C1 = "=If(R[-1]C="""",""Update Sheets"",""Second Description (""&INDIRECT(""'""&R[-2]C&""'!U2"") & "" something else)"")"
       .Cells(3 + 8 * w, 2).Formula = "=IF(R[-2]C[-1]="""","""",INDIRECT(""'""&R[-2]C[-1]&""'!U59""))"
       .Cells(4 + 8 * w, 1) = "Sales Tax" 'uncle sam gotta get his
       .Cells(4 + 8 * w, 2).FormulaR1C1 = "=Sum(R[-2]C:R[-1]C)*0.0825" 
       .Cells(5 + 8 * w, 1) = "Total amount w/tax"
     
       .Cells(5 + 8 * w, 2).FormulaR1C1 = "=Sum(R[-3]C:R[-1]C)"  '<----I'd like to preserve this value and add it to my total line
     
       .Cells(6 + 8 * w, 1) = "Other important descriptor"
       .Cells(6 + 8 * w, 2).FormulaR1C1 = "=IF(R[-5]C[-1]="""","""",Indirect(""'""&R[-5]C[-1]&""'!W58""))"
       .Cells(7 + 8 * w, 1).FormulaR1C1 = "=If(R[-6]C="""",""Update Sheets"",""Another descriptor (""&INDIRECT(""'""&R[-6]C&""'!U2"") & ""w/ cheese)"")"
       .Cells(7 + 8 * w, 2).FormulaR1C1 = "=IF(R[-6]C[-1]="""","""",INDIRECT(""'""&R[-6]C[-1]&""'!V59""))"
       .Range("A" & 2 + 8 * w & ":B" & 7 + 8 * w).HorizontalAlignment = xlRight
       .Range("A" & 2 + 8 * w & ":B" & 7 + 8 * w).Style = "Currency"
              
      End With

     w = w + 1
    Next i

'Probably add my total line here?

    With objNewWorksheet
      '   .Rows(1).Insert
         .Cells(1, 1) = "INDEX"
         .Cells(1, 1).Font.Bold = True
         .Cells(1, 2) = "NAME"
         .Cells(1, 2).Font.Bold = True
         .Columns("A:B").AutoFit
    End With
End Sub

 

[Artik]

Try:

    Dim strAdrs As String

(...)

       .Cells(5 + 8 * w, 2).FormulaR1C1 = "=Sum(R[-3]C:R[-1]C)"  '<----I'd like to preserve this value and add it to my total line
       strAdrs = strAdrs & .Cells(5 + 8 * w, 2).Address(0, 0) & ","

(...)

    'Probably add my total line here?
    strAdrs = Left(strAdrs, Len(strAdrs) - 1)
    Sheet?.Cells(?, ?).Formula = "=SUM(" & strAdrs & ")"

Artik

 

[Kenny_D]

That was beautiful! Thank you for your help!