SUM same cell range across different sheets

[glottis]

Hello,

as per title I want to create a new sheet in my workbook and in this new sheets adding cells that are the sum of the cells of the previous 3 sheets but the cycle for doesn't run. This is what I wrote:

  Set wdExl = Workbooks.Open(xlFileName) 'open Excel file
            
              AFO = wbNew3.Worksheets(1).Range("B11").Value
             MsgBox ("Variable = " & AFO)
        
        
  Dim SheetOne As Integer
  Dim SheetTwo As Integer
  Dim SheetThree As Integer
  
SheetOne = InputBox("What's the first sheet?")
SheetTwo = InputBox("What's the second sheet?")
SheetThree = InputBox("What's the third sheet?")
             

      
   With wdExl

Set cell_range = Range("B7:B14")

Dim Ano As Integer

SheetCount = .Worksheets.Count
.Worksheets.Add After:=.Worksheets(SheetCount)
 
 MsgBox ("SheetNo1 = " & SheetOne)
 
For Each Cell In cell_range

.Worksheets(SheetThree + 1).Cell.Value = .Worksheets(SheetOne).Cell.Value + .Worksheets(SheetTwo).Cell.Value + .Worksheets(SheetThree).Cell.Value
 
End With

Thank you

 

[mart37]

.Worksheets(SheetThree + 1).Cell.Value ==> .Worksheets(SheetThree + 1).Range(Cell.Address).Value

 

[glottis]

.Worksheets(SheetThree + 1).Cell.Value ==> .Worksheets(SheetThree + 1).Range(Cell.Address).Value

Should I put it inside the for cycle?If yes, syntax error comes up. Thank you

 

[Takae]

Set cell_range = Range("B7:B14")
This range is in activesheet of wdExl, not in Worksheets(SheetThree + 1). Is this correct? I think Worksheets(SheetThree + 1) is the newest sheet isn't it?

Dim Ano As Integer

SheetCount = .Worksheets.Count
.Worksheets.Add After:=.Worksheets(SheetCount)
Set cell_range = Range("B7:B14")

MsgBox ("SheetNo1 = " & SheetOne)

For Each Cell In cell_range
    .Worksheets(SheetThree + 1).Cell.Value = .Worksheets(SheetOne).Range(Cell.Address).Value + .Worksheets(SheetTwo).Range(Cell.Address).Value + .Worksheets(SheetThree).Range(Cell.Address).Value
next
End With

And you need to put "Next" in front of End with.

 

[mart37]

.Worksheets(SheetThree + 1).Range(Cell.Address).Value = .Worksheets(SheetOne).Range(Cell.Address).Value + .Worksheets(SheetTwo).Range(Cell.Address).Value + .Worksheets(SheetThree).Range(Cell.Address).Value

 

[glottis]

.Worksheets(SheetThree + 1).Range(Cell.Address).Value = .Worksheets(SheetOne).Range(Cell.Address).Value + .Worksheets(SheetTwo).Range(Cell.Address).Value + .Worksheets(SheetThree).Range(Cell.Address).Value

Itworked! Thanks a lot