Formulas referencing CodeName in a macro

[HURTMYPONY]

I have an Excel workbook that uses a macro to populate many sheets with formulas.

The formulas reference several different source pages (5), and are mostly variants of COUNTIFS. Here’s a snippet:

    Sheet1.Select
    Range("C5").Select
    ActiveCell.FormulaR1C1 = _
        "=COUNTIFS(Source!C9,R[-1]C,Source!C1,""<>"")+COUNTIFS(Source2!C9,R[-1]C,Source2!C1,""<>"")+COUNTIFS(Source3!C9,R[-1]C,Source3!C1,""<>"")+COUNTIFS(Source4!C9,R[-1]C,Source4!C1,""<>"")*COUNTIFS(Source5!C9,R[-1]C,Source5!C1,""<>"")"
    Range("C6").Select
    ActiveCell.FormulaR1C1 = _
        "=COUNTIFS(Source!C9,R[-2]C,Source!C2,""<>"")+COUNTIFS(Source2!C9,R[-2]C,Source2!C2,""<>"")+COUNTIFS(Source3!C9,R[-2]C,Source3!C2,""<>"")+COUNTIFS(Source4!C9,R[-2]C,Source4!C2,""<>"")+COUNTIFS(Source5!C9,R[-2]C,Source5!C2,""<>"")"
    Range("C8").Select
    ActiveCell.FormulaR1C1 = "=R[-3]C-R[-1]C"
    Range("C9").Select
    ActiveCell.FormulaR1C1 = _
        "=COUNTIFS(Source!C9,R[-5]C,Source!C18,""L"")+COUNTIFS(Source2!C9,R[-5]C,Source2!C18,""L"")+COUNTIFS(Source3!C9,R[-5]C,Source3!C18,""L"")+COUNTIFS(Source4!C9,R[-5]C,Source4!C18,""L"")+COUNTIFS(Source5!C9,R[-5]C,Source5!C18,""L"")"
    Range("C10").Select
    ActiveCell.FormulaR1C1 = _
        "=COUNTIFS(Source!C9,R[-6]C,Source!C19,""F"")+COUNTIFS(Source2!C9,R[-6]C,Source2!C19,""F"")+COUNTIFS(Source3!C9,R[-6]C,Source3!C19,""F"")+COUNTIFS(Source4!C9,R[-6]C,Source4!C19,""F"")+COUNTIFS(Source5!C9,R[-6]C,Source5!C19,""F"")"

The initial problem was the sheets containing the source data (appearing above as "Source, Source2, etc.) get their sheet names changed between uses by the users, which would break the formulas the macro placed the next time the sheet was used, since the formulas the macro placed the next time would be referencing a sheet name that no longer existed.

I "fixed" this by using this code at the start of the macro to change the sheet names back to something the formulas recognized:

    Sheet3.Name = Worksheets("Store Setup Sheet").Range("A2")
    Sheet2.Name = Worksheets("Store Setup Sheet").Range("A3")
    Sheet8.Name = Worksheets("Store Setup Sheet").Range("A4")
    Sheet4.Name = Worksheets("Store Setup Sheet").Range("A5")
    Sheet10.Name = Worksheets("Store Setup Sheet").Range("A6")
    Sheet13.Name = Worksheets("Store Setup Sheet").Range("A9")
    Sheet14.Name = Worksheets("Store Setup Sheet").Range("A10")
    Sheet15.Name = Worksheets("Store Setup Sheet").Range("A11")
    Sheet16.Name = Worksheets("Store Setup Sheet").Range("A12")
    Sheet17.Name = Worksheets("Store Setup Sheet").Range("A13")

This puts the sheet names back to their original names. Next, the macro places the formulas on all the sheets and populates them across a dynamic number of columns.

Then, at the end of the macro, I use something similar to change the sheet names back to what the users had changed the sheet name to.

This works fine in theory.

But the macro now takes too long to run.

When the macro changes the sheet names back to the user's preference at the end of its process, Excel takes a few minutes to change the sheet name references of all those formulas I just placed, which takes minutes on slower machines.

I need a cleaner, quicker solution but I am not sure how to do it.

Can I somehow reference a sheet's CodeName in a formula like above?

If I could have my formulas operate on the CodeName, I wouldn't need to mess around with changing (and changing back) all the sheet names, but I have no idea on how to do this.

If there is a solution to using sheet CodeNames in formulas within macros, please point me in the right direction!

Thank you!