VBA/Macro to insert text, formulas, and sum

[beaglej1]

I collect information from about 200 constituents using an excel ‘form’. Each file has a “Department Only” tab where we are trying to total some of the other tabs. I anticipate there could be up to ten tabs.

I would like to use a macro or VBA to place the potential name of each tab (below) in column B starting in cell B40.
Part II-SubContracts
Part II-SubContracts (2)
Part II-SubContracts (3)
Part II-SubContracts (4)
Part II-SubContracts (5)
Part II-SubContracts (6)
Part II-SubContracts (7)
Part II-SubContracts (8)
Part II-SubContracts (9)
Part II-SubContracts (10)

In columns C and D, I would like to it to automatically paste the two formulas. These formulas are grabbing totals in cell ay5 and ay7 from each subcontracts tab based on the tab name which was inserted in the first step.

In cell C40, the formula would be
=IF(ISERROR(INDIRECT("'"&B40&"'!ay5")),0,INDIRECT("'"&B40&"'!ay5"))

In cell D40, the formula would be
=IF(ISERROR(INDIRECT("'"&B40&"'!ay5")),0,INDIRECT("'"&B40&"'!ay7"))

The formulas would, of course, be repeated for all 10 tabs. (See attached)

I would, then, like to sum these amounts in cells C50 and D50.

From there, take these totals and make them part of the Totals table above. In cell B30, the formula would be =-1*C50 and in cell E30, the formula would be =-1*D50.

I don’t know if this is pertinent, but I will also need to be able to share these macros/VBA with colleagues.

I'm sure there is an easier way to do this, but this is the best we could come up with given our timeline. Any help is greatly appreciated.
Thank you!

A link to the file:
https://bit.ly/2MkAkj1

 

[DanteAmor]

Try this

Sub Insert_Text_Formulas()
    Dim sh As Worksheet
    Dim t As String, r As Long, i As Long
    
    Set sh = Sheets("Department Only")


    t = "Part II-SubContracts"
    sh.Range("B40").Value = t
    r = 41
    For i = 2 To 10
        sh.Range("B" & r).Value = t & "(" & i & ")"
        r = r + 1
    Next
    
    With sh.Range("C40:C49")
        .Formula = "=IF(ISERROR(INDIRECT(""'"" & B40 & ""'!AY5"")),0,INDIRECT(""'"" & B40 & ""'!AY5""))"
    End With
    With sh.Range("D40:D49")
        .Formula = "=IF(ISERROR(INDIRECT(""'"" & B40 & ""'!AY9"")),0,INDIRECT(""'"" & B40 & ""'!AY9""))"
    End With
    With Range("C50:D50")
        .FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"
    End With
    Range("B30").Formula = "=-1 * C50"
    Range("E30").Formula = "=-1 * D50"


End Sub

 

[beaglej1]

YES!!!!! Thank you so very much! Wow. This saved me a ton of time. I am so very grateful. Thank you!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.