Macro Sheet & Formula Name Issues

Hi All,

I have recorded a Macro to save a coworker some time when she is going a report. The Macro functions perfectly. The problem is that the report is ran from a saved excel file from an odd program. You have to save the report to your computer, and then open it in excel and then run the macro. However whatever you save the report as, that becomes the name of the sheet that the data is stored on. So unless the report is saved as the same name each and evertime it is ran the Macro stops working because the sheet references, and formulas are referencing the name of the report I used when I recorded the macro. Is there any way to change the code to something else so that the report can be saved with any name, and the macro still work?

Thank You

Can you use the sheet's index number instead of name? Like:
Sheets(1) instead of Sheets("Sheet1").

Change it to:

ActiveCell.FormulaR1C1 = "=SUMIF(" & Sheets(1).Name & "C[10],RC[-1]&"" Regular""," & Sheets(1).Name & "C[2])"

ActiveCell.FormulaR1C1 = "=SUMIF('" & Sheets(1).Name & "'!C[10],RC[-1]&"" Regular"",'" & Sheets(1).Name & "'!C[2])"

I forgot the single quotes around the sheet name and the exclamation point too.