Sum across sheets

Status
Not open for further replies.

glottis

New Member
Joined
Oct 21, 2011
Messages
11
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:


VBA 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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,398
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.
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,129,368
Messages
5,635,856
Members
416,886
Latest member
coreyalaurence37

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top