Forum Discussion

Natasha Botha's avatar
Natasha Botha
Copper Contributor
Mar 29, 2018

calculation with code

HI Guys,   I hope you can help. Someone previously helped me with a code to automatically generate sheets based on the nr that you enter in a cell. Which by the way works awesomely! See code belo...
Formulas & Functions
Need Help
Natasha Botha's avatar
Natasha Botha
Copper Contributor
Apr 03, 2018

Hi Matt, 

 

Please see attached. 

 

The "do not use" sheet is the source for the duplication of sheets (based on the number that is entered in cell J10)

 

The summary then have to be calculated on the "cover" sheet based on the sum of a cell from all sites. I've numbered the cells that needs to be calculated. 

 

I hope this helps

 

Thank you

Natasha

Matt Mickle's avatar
Matt Mickle
Bronze Contributor
Apr 03, 2018

Natasha-

 

Try incorporating this procedure into your workflow.  It dynamically updates your formulas.  You would want to run this procedure after you add the "Site" sheets.

 

Sub UpdateFormulas()

    Dim sht As Worksheet
    Dim shtCount As Integer
    Dim Counter As Integer
    Dim Formula1 As String
    Dim Formula2 As String
    Dim Formula3 As String
    Dim Formula4 As String
    Dim Formula5 As String
    Dim Formula6 As String
    Dim Formula7 As String
    Dim Formula8 As String
    Dim Formula9 As String
    
    shtCount = ActiveWorkbook.Worksheets.Count
    For Each sht In ActiveWorkbook.Worksheets
    
        'Increment counter to keep track of sheet count
        Counter = Counter + 1
        
        'Exclude these worksheets from the formula
        If sht.Name = "Cover Page" Or _
           sht.Name = "Test 1" Or _
           sht.Name = "Test 2" Or _
           sht.Name = "Test 3" Or _
           sht.Name = "DO NOT USE" Then
        GoTo Skip
        End If
           
        'Create Formula Strings
        Formula1 = Formula1 & "'" & sht.Name & "'!Y122,"
        Formula2 = Formula2 & "'" & sht.Name & "'!Y123,"
        Formula3 = Formula3 & "'" & sht.Name & "'!Y124,"
        Formula4 = Formula4 & "'" & sht.Name & "'!Y125,"
        Formula5 = Formula5 & "'" & sht.Name & "'!Y127,"
        Formula6 = Formula6 & "'" & sht.Name & "'!Y129,"
        Formula7 = Formula7 & "'" & sht.Name & "'!Z131,"
        Formula8 = Formula8 & "'" & sht.Name & "'!Z123,"
        Formula9 = Formula9 & "'" & sht.Name & "'!Z134,"
      
        
        'If we are on the final worksheet then we want to input the formula
        If Counter = shtCount Then
            
           'Trim the extra comma off the formula
           Formula1 = Mid(Formula1, 1, Len(Formula1) - 1)
           Formula2 = Mid(Formula2, 1, Len(Formula2) - 1)
           Formula3 = Mid(Formula3, 1, Len(Formula3) - 1)
           Formula4 = Mid(Formula4, 1, Len(Formula4) - 1)
           Formula5 = Mid(Formula5, 1, Len(Formula5) - 1)
           Formula6 = Mid(Formula6, 1, Len(Formula6) - 1)
           Formula7 = Mid(Formula7, 1, Len(Formula7) - 1)
           Formula8 = Mid(Formula8, 1, Len(Formula8) - 1)
           Formula9 = Mid(Formula9, 1, Len(Formula9) - 1)
     
           'Put new formula on Cover Page Worksheet
           Sheets("Cover Page").Range("G18") = "=Sum(" & Formula1 & ")"
           Sheets("Cover Page").Range("G20") = "=Sum(" & Formula2 & ")"
           Sheets("Cover Page").Range("G22") = "=Sum(" & Formula3 & ")"
           Sheets("Cover Page").Range("G24") = "=Sum(" & Formula4 & ")"
           Sheets("Cover Page").Range("G27") = "=Sum(" & Formula5 & ")"
           Sheets("Cover Page").Range("G30") = "=Sum(" & Formula6 & ")"
           Sheets("Cover Page").Range("G32") = "=Sum(" & Formula7 & ")"
           Sheets("Cover Page").Range("G35") = "=Sum(" & Formula8 & ")"
           Sheets("Cover Page").Range("G38") = "=Sum(" & Formula9 & ")"
           
        End If
Skip:
    Next sht

End Sub

 

Resources