help with macro issue

[bigmac1]

hello all, I have come across the macro below, this allows me to look at the cell(A10)
on all sheets in my workbook even new ones (except sheet1) and ad the contents of all sheets together
then put the total in A10 on sheet1, this works great BUT I want it to work over a lot of cells,
example A1 TO J22 and place the result of each cell in the corresponding cells in sheet1.
can you help a newbie please

Sub updatethesum()

Dim ws As Worksheet
Dim i As Double
i = 0
Dim bletter As Boolean

For Each ws In ThisWorkbook.Worksheets

If ws.Name <> "Sheet1" Then bletter = IsNumeric(ws.Range("A10").Value)
If bletter = True Then
i = i + ws.Range("A10")
End If
End If

Next
Sheets("Sheet1").Range("A10") = i
End Sub​

 

[Peter_SSs]

Here is another approach that may suit you.
Make sure that Sheet1 is the left hand sheet of your workbook and then try this macro.

Sub SumAcrossWorksheets()
  With Sheets("Sheet1").Range("A1:J22")
    .FormulaR1C1 = "=SUM('" & Sheets(2).Name & ":" & Sheets(Sheets.Count).Name & "'!RC)"
    .Value = .Value
  End With
End Sub

 

[Michael M]

My offer....although Peters will be much quicker

Sub MM1()
Dim c As Long, r As Long, ws As Worksheet, n As Long
For r = 1 To 22
    For c = 1 To 10
        n = 0
        For Each ws In Worksheets
            If ws.Name <> "Sheet1" Then
                n = n + ws.Cells(r, c).Value
            End If
        Next ws
        Sheets("Sheet1").Cells(r, c).Value = n
    Next c
Next r
End Sub

 

[Peter_SSs]

My offer....although Peters will be much quicker

I'm not sure there is much difference in speed, but it seems to me to be a simpler concept to do all cells at once without looping both sheets and cells.


BTW, your declaration of n as Long is making an assumption about the numerical data that was not made by the OP's originally posted code.

 

[Michael M]

your declaration of n as Long is making an assumption about the numerical data

Thanks Peter, it WAS an assumption that may have been wrong....or not, depending on the data.
I also wondered if EVALUATE would be able to be used here....but for the life of me, EVALUATE does my head in, and I struggle with it constantly.
It doesn't seem that hard but I never seem to be able to get the syntax right straight away !!

 

[bigmac1]

thank you peter_SSs and Michael M for your quick responses, I have tried michaels code and it worked great , I used his because as a newbie I could just get my head around it , but I will try peters later, thanks

 

[Peter_SSs]

You're welcome. Seems like a lot of looping to me but you do need to choose the method that suits you the best. So as long as it works and you are happy with it, that's all that matters.