Project #4: Loop the SUM() Function Over Multiple Worksheets

 To loop the SUM() function over multiple worksheets in Excel using VBA, we'll create a macro that iterates through each worksheet in the workbook, calculates the sum for a specified range on each worksheet, and displays the total sum.

Here's the VBA code to accomplish this:

Sub SumAcrossWorksheets() 
Dim ws As Worksheet 
Dim sumRange As Range 
Dim totalSum As Double 
' Define the range to sum (adjust as needed) 
' For example, summing range A1:A10 on each worksheet 
Set sumRange = Range("A1:A10") 
' Initialize totalSum 
totalSum = 0 
' Loop through each worksheet in the workbook 
For Each ws In ThisWorkbook.Worksheets 
' Check if the worksheet is not empty 
If WorksheetFunction.CountA(ws.Cells) <> 0 Then 
' Calculate sum for the specified range on the current worksheet 
totalSum = totalSum +Application.WorksheetFunction.Sum(ws.Range(sumRange.Address))
        End If 
Next ws 
' Display the total sum 
MsgBox "Total sum across all worksheets: " & totalSum 
End Sub

In this macro:

ws is a Worksheet object representing each worksheet in the workbook.

sumRange is a Range object representing the range to sum on each worksheet. Adjust this range as needed.

totalSum is a variable to store the total sum across all worksheets.

The loop iterates through each worksheet in the workbook using a For Each loop.

For each worksheet, it checks if the worksheet is not empty (using CountA function).

If the worksheet is not empty, it calculates the sum for the specified range on the current worksheet and adds it to the totalSum.

Finally, it displays the total sum across all worksheets in a message box.

You can run this macro to quickly calculate the total sum across multiple worksheets in your workbook, based on the specified range. Adjust the range as needed to fit your data.