Not Getting Sum Result From All Sheets

[prasadk]

Hi Excel Friends

I have applied this vba to get sum result from all sheets into main sheet in workbook here what the problem i have getting when i run this vba i will getting sum result from one sheet only

I Need to get Total Sum Result From all Sheet the Range("D3 to D7") to Cell ("B2") in main sheet


This is the Code i applied







Sub SumMul()


Dim wb As Workbook, ws As Worksheet, ar, i As Double

Set wb = ThisWorkbook
ReDim ar(1 To wb.Sheets.Count, 1 To 1)


For Each ws In wb.Sheets
If ws.Name <> "main" Then
i = i + 1
ar(i, 1) = Application.WorksheetFunction.Sum(ws.Range("D3:D7"))
End If
Next

wb.Sheets("main").Range("B2").Value = ar



End Sub

[p45cal]

Depending on what you want, you can try replacing your:

wb.Sheets("main").Range("B2").Value = ar
with:

wb.Sheets("main").Range("B2").Value = Application.Sum(ar)
if you want a single total in a single cell


or:

wb.Sheets("main").Range("B2").Resize(i).Value = ar
if you want a row per sheet total starting at cell B2.

[prasadk]

Thank you so much @p45cal