Forum Discussion
calculation with code
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).
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 MickleApr 03, 2018Bronze Contributor
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