I have created a structured spreadsheet (not filterable) for reporting of
various sales by industry. I have also added macros which displays only rows
related to specific industry groups. To ensure the totals sum only the
visible cells I have added the attached function code to the spreadsheet
which I sourced from the Microsoft resources and another site;

Function Sum_Visible(Cells_To_Sum As Object)
Application.Volatile
For Each cell In Cells_To_Sum
If cell.Rows.Hidden = False Then
If cell.Columns.Hidden = False Then
total = total + cell.Value
End If
End If
Next
Sum_Visible = total
End Function

The function works fine as long as the except that the total it provides is
based on the cells referred to in the function but on the active worksheet.
So for example if the function is used in sheet 2 and sheet 3 but
application.calculate is run while in sheet 1, the functions sums the
relevant cells selected on sheet1.

The only other relevant thing is that the function is applied to a
non-continguous range, if that makes any difference.

I have created a workaround by forcing application.calculate each time a
sheet is selected. It's a little sloppy but at least the function is working.

Any assistance would be greatly appreciated.