Sum cells within a range of cells from multiple worksheets to a master worksheet

[kevinkangz1]

Hello,

I'm a novice in Excel, please help on below situation:

From multiple worksheets, within a range of cells I want each cells to be added to a master worksheet.

For example,
from A1 to A8 in multiple worksheets, I want A1.value in multiple sheets to be added to Master worksheet.
and A2.Value in multiple sheets to be added to Master worksheet.

I understand below code will work:

Private Sub CommandButton1_Click()

Dim i As Long
Dim x As Long

x = 0

For i = 1 To ActiveWorkbook.Worksheets.Count

    x = x + Sheets(i).Range("J27").Value2 'Multiple worksheets
    
Next i

Sheet1.Range("A1").Value2 = x 'Master Worksheet/workbook

End Sub

But below code will only work for one cell only..
How can I do it for all cells within a range? I don't want to repeat above code for each cell to be added to the master worksheet, as there are more than 400 cells in my table.

Thank you

 

[Yongle]

are you aware that you can sum by formula between the first sheet (here named January-17) and last sheet (here named April-17)

formula in Master
=SUM('January-17:April-17'!J27)