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 below:

 

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("J9")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Dim x As Long
For x = 1 To Range("J9").Value
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
ActiveSheet.Name = "Site " & x
Range("J9").ClearContents
Next x
Application.ScreenUpdating = True
End Sub

 

The next step is to have a summary page of these sheets. The problem is (I only know basic formulas) but I cannot really do the formula on the summary page if the sheets are not there yet?

 

The idea is to have a base sheet - Telling excel to duplicate the sheets based on the number of sites the client have. 

I would then do the necessary calculations and options on the sheets for each site, and at the summary page I want to calculate all sites total balances 

 

I hope this makes sense

 

Thanks

 

 

 

 

Formulas & Functions
Need Help

3 Replies

  • 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