Sum across sheets

[glottis]

Hello,

I am trying to write a program that export to an existing excel document (let's call it EXCELA) some tables from a Word document and then let add a new sheet to EXCELA adding as value the sum of the current export to the value of the previous sheets of EXCELA for a specific range (B7:B14) . The issue I got now is to make this sum working.
COuld you kindly advose please?

Thanks a lot!!!
Here below my code:

Sub ImportWordTable()

    Dim wdDoc As Object
    Dim wdFileName As Variant
    Dim tableNo As Integer 'table number in Word
    Dim iRow As Long 'row index in Excel
    Dim iCol As Integer 'column index in Excel
    Dim resultRow As Long
    Dim tableStart As Integer
    Dim tableTot As Integer
    Dim allTables As Collection '<<

    On Error Resume Next

    ActiveSheet.Range("A:AZ").ClearContents

    wdFileName = Application.GetOpenFilename("Word files (*.docx),*.doc", , _
    "Browse for file containing table to be imported")

    If wdFileName = False Then Exit Sub '(user cancelled import file browser)

    Set wdDoc = GetObject(wdFileName) 'open Word file

    Set allTables = GetTables(wdDoc)  '<<< see function below

    tableNo = allTables.Count
    tableTot = allTables.Count
    If tableNo = 0 Then
        MsgBox "This document contains no tables", _
        vbExclamation, "Import Word Table"
    ElseIf tableNo > 1 Then
        tableNo = InputBox("This Word document contains " & tableNo & " tables." & vbCrLf & _
        "Enter the table to start from", "Import Word Table", "1")
    End If

    resultRow = 4

    For tableStart = 1 To tableTot
        With allTables(tableStart)
            'copy cell contents from Word table cells to Excel cells
            For iRow = 1 To .Rows.Count
                For iCol = 1 To .Columns.Count
                    Cells(resultRow, iCol) = WorksheetFunction.Clean(.Cell(iRow, iCol).Range.Text)
                Next iCol
                resultRow = resultRow + 1
            Next iRow
        End With
        resultRow = resultRow + 1
    Next tableStart


Dim wbNew3 As Variant
        
     wbNew3 = Application.GetSaveAsFilename
       If wbNew3 <> False Then

ActiveWorkbook.SaveAs Filename:=wbNew3

Set wbNew3 = ActiveWorkbook


choice = InputBox("Save sheet 1, Sum up 3 months 2, Sum up 6 months 3")

If choice = 2 Then

             xlFileName = Application.GetOpenFilename("Excel files (*.xlsx),*.xlsx", , _
            "Browse for file containing sheets to be imported")

           ' If xlFileName = False Then Exit Sub '(user cancelled import file browser)

            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?")
             

        '  wdExl.Sheets.Add
   With wdExl

Dim SheetCount As Integer
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


   End If
   End If

 

[glottis]

Solved via another threads in the forum. thanks

 

[smozgur]

Solved via another threads in the forum. thanks

Glad to hear you got the solution.

Could you please let us know which thread(s) helped you? Then it is perfectly fine to mark your post as the solution to help future readers.

 

[glottis]

Glad to hear you got the solution.

Could you please let us know which thread(s) helped you? Then it is perfectly fine to mark your post as the solution to help future readers.

sure! This post: SUM same cell range across different sheets

 

[Fluff]

Duplicate to: SUM same cell range across different sheets

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.