CODEWITHSUNDEEP
excelvbasumworksheet-function
Darshan Kadam
Darshan Kadam

Reputation: 3

Sum of same cell (C3) in each available worksheet in VBA

I was tried to used this code as i need to sum from second sheet till available sheet in workbook

Result = Application.WorksheetFunction.Sum(Worksheets("Second SHEET:FINAL SHEET").Range("C3"))

Upvotes: 0

Views: 47

Answers (1)

JvdV
JvdV

Reputation: 75990

I think what you are after is the Application.Evaluate method. This gives you the possibility to emulate a calculation like:

=SUM('Second Sheet:Final Sheet'!C3)

To return the 2nd and last worksheet we can look at the different indexes withing the WorkSheets collection and build a valid string to be used in Evalate:

Sub Test()

With ThisWorkbook
    Dim Nom2d As String: Nom2d = .Worksheets(2).Name
    Dim NomLw As String: NomLw = .Worksheets(.Worksheets.Count).Name
End With

Dim result As Double: result = Evaluate("SUM('" & Nom2d & ":" & NomLw & "'!C3)")

End Sub

Provided you haven't tweaked around the WorkSheets their indexes, this should do the trick.

EDIT: If your sheets are actually called Second SHEET and FINAL SHEET, it's a bit easier:

Dim result As Double: result = Evaluate("SUM('Second SHEET:FINAL SHEET'!C3)")

Upvotes: 1

Related Questions