Summary Tab VBA: Using formulas taken from new tabs

[eg8]

Hi All-

I am currently working through a macro and got stuck about halfway. I have a number of files in a folder on my drive that I am pulling the first tab from into a Master workbook, and then I want to have a summary tab for all of those tabs(they are all identical). Some of the cells will be text(say range A5:C105), some will be SUM(E6:G105) and some will be AVERAGE(D6:D104) formulas needed. These formulas will not change, but will need to pull the data from all tabs that are pulled into the file.

So far I have this code that pulls all of the first tabs together:

Sub Staff_Plan_Update()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim MyPath As String
Dim strFilename As String
    
    Call TimeStamp
    
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    MyPath = "C:\Users\Documents\Staff Plans\" ' change to current path
    Set wbDst = Workbooks.Add(xlWBATWorksheet)
    strFilename = Dir(MyPath & "\*.xlsx", vbNormal)
    
    
    If Len(strFilename) = 0 Then Exit Sub
    
    Do Until strFilename = ""
                   
            Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFilename)
            
            Set wsSrc = wbSrc.Worksheets(1)
            
            wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)
            
            If Target = Range("A1") Then
            Sheets(1).Name = Range("A1").Value
            End If
            
            wbSrc.Close False
        
        strFilename = Dir()
        
        
    Loop
    wbDst.Worksheets(1).Delete
    
    
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True

    
    MsgBox ("Staff plan update is complete.")
    
        
    
End Sub

I was going to record a macro that creates a summary table every time, but not sure if it is easier to create a blank template for the summary tab that will update every time all of the tabs are pulled into this file. The problem I ran across with that is that I will be taking the SUM of all tabs, but the number of tabs/name of tabs will be different.

Appreciate any help and can clarify if that didn't come across clearly. Thanks!!

 

[eg8]

UPDATE:

I went through and created the MASTER summary sheet by recording the macro, and it seems to work, the only problem is that I can't get one of my sums to pull all tabs.

As the tabs are pulled into the summary they are named "FTE Summary" then "FTE Summary(1)" and so on. I want the formula to pull "=SUM('FTE SUMMARY:FTE SUMMARY (#)'!RC)" with # being the last tab in the workbook. Here is the full code, with the selection I want to update highlighted:

Sub Create_Master()
Dim wrk As Workbook  'Workbook object - Always good to work with object variables
Dim sht As Worksheet 'Object for handling worksheets in loop
Dim trg As Worksheet 'Master Worksheet
Dim rng As Range     'Range object
Dim colCount As Integer 'Column count in tables in the worksheets
    
    Set wrk = ActiveWorkbook 'Working in active workbook
    
    For Each sht In wrk.Worksheets
        If sht.Name = "Master" Then
            MsgBox "There is a worksheet called as 'Master'." & vbCrLf & _
                    "Please remove or rename this worksheet since 'Master' would be" & _
                    "the name of the result worksheet of this process.", vbOKOnly + vbExclamation, "Error"
            Exit Sub
        End If
    Next sht
    
    'We don't want screen updating
    Application.ScreenUpdating = False
    
    'Add new worksheet as the last worksheet
    Set trg = wrk.Worksheets.Add(After:=wrk.Worksheets(wrk.Worksheets.Count))
    'Rename the new worksheet
    trg.Name = "Master"
    Sheets("FTE SUMMARY").Select
    Cells.Select
    Selection.Copy
    Sheets("MASTER").Select
    Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "MASTER"
    Range("E6").Select
    Sheets("FTE SUMMARY").Select
    ActiveWindow.SmallScroll Down:=-21
    Range("D6:G105").Select
    Selection.Copy
    Sheets("MASTER").Select
    ActiveWindow.SmallScroll Down:=-24
    Sheets("FTE SUMMARY").Select
    ActiveWindow.SmallScroll Down:=-96
    Sheets("MASTER").Select
    Range("D6").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("I6:M104").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=87
    Range("I105:M106").Select
    Selection.ClearContents
    Sheets("FTE SUMMARY").Select
    ActiveWindow.SmallScroll Down:=78
    Range("I106").Select
    ActiveWindow.SmallScroll Down:=-102
    Range("I7:I11").Select
    Sheets("MASTER").Select
    ActiveWindow.SmallScroll Down:=-117
    Range("I6").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R[5]C)"
    Range("I6").Select
    Selection.Copy
    Range("J6:M6").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("I12").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R[8]C)"
    Range("I12").Select
    Selection.Copy
    Range("J12:M12").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("I21").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R[20]C)"
    Range("I21").Select
    Selection.Copy
    Range("J21:M21").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("I42").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R[24]C)"
    Range("I42").Select
    Selection.Copy
    Range("J42:M42").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("I67").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R[2]C)"
    Range("I67").Select
    Selection.Copy
    Range("J67:M67").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=27
    Range("I70").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R[5]C)"
    Range("I70").Select
    Selection.Copy
    Range("J70:M70").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("I76").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R[12]C)"
    Range("I76").Select
    Selection.Copy
    Range("J76:M76").Select
    ActiveWindow.SmallScroll Down:=-18
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("I89").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R[9]C)"
    Range("I89").Select
    Selection.Copy
    Range("J89:M89").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("I99").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R[5]C)"
    Range("I99").Select
    Selection.Copy
    Range("J99:M99").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Sheets("FTE SUMMARY").Select
    ActiveWindow.SmallScroll Down:=81
    Range("I6:I104").Select
    Range("I104").Activate
    ActiveWindow.SmallScroll Down:=84
    Range("J104").Select
    Range(Selection, Selection.End(xlUp)).Select
    Sheets("MASTER").Select
    Range("I105").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM(R[-99]C:R[-1]C)/2"
    Range("I105").Select
    Selection.Copy
    Range("J105:M105").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Sheets("MASTER").Select
    Range("I106").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=R[-1]C*150"
    Range("I106").Select
    Selection.Copy
    Range("J106:M106").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=-162
    Range("I7").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM('FTE SUMMARY:FTE SUMMARY (2)'!RC)"
    Range("I7").Select
    Selection.Copy
    Range("J7:M7").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("I7:M7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("I8:M11,I13:M20").Select
    Range("I13").Activate
    ActiveWindow.SmallScroll Down:=15
    Range("I8:M11,I13:M20,I22:M41").Select
    Range("I22").Activate
    ActiveWindow.SmallScroll Down:=18
    Range("I8:M11,I13:M20,I22:M41,I43:M66").Select
    Range("I43").Activate
    ActiveWindow.SmallScroll Down:=24
    Range("I8:M11,I13:M20,I22:M41,I43:M66,I68:M69,I71:M75,I77:M88").Select
    Range("I77").Activate
    ActiveWindow.SmallScroll Down:=21
    Range( _
        "I8:M11,I13:M20,I22:M41,I43:M66,I68:M69,I71:M75,I77:M88,I90:M98,I100:M104"). _
        Select
    Range("I100").Activate
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=-135
    ActiveWindow.ScrollColumn = 1
End Sub

Any ideas on how I can have that select all tabs, rather than just the first two which I did manually during the record?

Thanks!