Code: SUM new merged tabs

[eg8]

I am working through a macro and have one final update to make. The macro is merging worksheets from a specified folder and then creating a 'Master' Sheet which takes the SUM of a specified cell from all other sheets besides the Master. Currently I wrote into the code:

   Range("D7").Select
   ActiveCell.FormulaR1C1 = "=AVERAGE('FTE SUMMARY:FTE SUMMARY (8)'!RC)"

The problem is (8) may not always be the final FTE summary. There will be times where there are up to 20 sheets it is pulling off of and it will always be a different number.

Is there a way to have that pull from all active sheets except the 'Master' sheet? Appreciate any advising!!

Here is more of the code if it helps, but it was a manual record:

Sub Master_Update()
'
' Master_Update Macro
'

'
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveCell.FormulaR1C1 = "MASTER"
    Range("A2").Select
    Sheets("Sheet10").Select
    Sheets("Sheet10").Name = "MASTER"
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("FTE SUMMARY").Select
    Columns("A:G").Select
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("MASTER").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("FTE SUMMARY").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("MASTER").Select
    Columns("A:G").Select
    ActiveSheet.Paste
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "MASTER"
    Range("A2").Select
    Selection.ClearContents
    Columns("C:C").Select
    Selection.EntireColumn.Hidden = True
    Range("D7").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE('FTE SUMMARY:FTE SUMMARY (8)'!RC)"
    Range("D7").Select
    Selection.Copy
    Range("D8:D11,D13:D20").Select
    Range("D13").Activate
    ActiveWindow.SmallScroll Down:=9
    Range("D8:D11,D13:D20,D22:D41").Select
    Range("D22").Activate
    ActiveWindow.SmallScroll Down:=21
    Range("D8:D11,D13:D20,D22:D41,D43:D66").Select
    Range("D43").Activate
    ActiveWindow.SmallScroll Down:=21
    Range("D8:D11,D13:D20,D22:D41,D43:D66,D68:D69,D71:D75,D77:D88").Select
    Range("D77").Activate
    ActiveWindow.SmallScroll Down:=24
    Range( _
        "D8:D11,D13:D20,D22:D41,D43:D66,D68:D69,D71:D75,D77:D88,D90:D98,D100:D104"). _
        Select
    Range("D100").Activate
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=-162
    Range("E7").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM('FTE SUMMARY:FTE SUMMARY (8)'!RC)"
    Range("E7").Select
    Selection.Copy
    Range("E8:E11,E13:E20").Select
    Range("E13").Activate

 

[eg8]

bump to see if anyone knows how to do this? It's the last piece of my macro and I can't seem to figure it out.

Thanks!!!
EG