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
Matt Mickle's avatar
Matt Mickle
Bronze Contributor
Apr 01, 2018

This is definitely possible using VBA.  In order for the community to be able to better assist you it would be helpful to have an example of what type of summary you want.  This would consist of a before and after.  I see that all of the sheets have generic names like Site 1, Site , Site 3 etc....  what does the data look like that you need a summary of?  You can't attach .xlsm files to this site due to security issues but if you attach an .xlsx and post your current along with a before and after it would greatly help the community to return an answer (Please Use Non-Sensitive Data).

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