使用多个工作表对来自另一个工作簿的值求和的宏 [英] Macro to sum values from another Workbook with Multiple Sheets

问题描述

我正在处理一个项目，该项目要求我对另一个 Excel 工作簿中的多个值求和.

I am working on a project that requires me to sum multiple values in another excel workbook.

另一个工作簿包含 120 页.我想从每张纸中取一个特定的值，并将其与其余的相加.所有 120 个值的单元格引用都是固定的，例如 J3.

The other Workbook contains, say, 120 sheets. I want to take a certain value from each sheet, and sum it along with the rest. The cell reference for all the 120 values is fixed, for example J3.

另一件值得一提的事情(也许)是工作表代码名称没有组织(即工作表 1 一直到工作表 120)，中间添加/删除了几个工作表.

Another thing worth mentioning (maybe) is that the sheet code names aren't organized (i.e. Sheet 1 all the way to Sheet 120), there have been several sheets added/removed in between.

现在我得到了

运行时错误 424 需要对象

Run Time Error 424 Object Required

Sub SumProject1P()
    Dim Project1P As Workbook
    Dim reserves
    Dim WS_Count As Integer
    Dim i As Integer
    Dim V As Variant

    Set Project1P = Workbooks.Open("FILE PATH")
    WS_Count = Workbook.Worksheets.Count
    V = Workbook.Worksheets
    reserves = sumrange(Workbook.Worksheets(V).range(Cells(1, 8)))
End Sub


Function sumrange(range)
    summ = 0
    For i = 1 To WS_Count
        summ = summ + reserves
    Next
    reserves = summ
End Function

我希望收到反馈和意见.

I hope to receive feedback and input.

推荐答案

给你

Sub SumProject1P()

Dim Project1P As Workbook
Dim reserves As Long
Dim WS_Count As Integer
Dim filePath As String

    filePath = "Enter you file path"

    Set Project1P = Workbooks.Open(filePath, ReadOnly:=True)
    WS_Count = Project1P.Worksheets.Count


   For i = 1 To WS_Count
    If Sheets(i).range("J3") <> "" And IsNumeric(Sheets(i).range("J3")) Then
        reserves = reserves + Sheets(i).range("J3")
    End If
   Next

   MsgBox "Total of all sheets :" & reserves
End Sub

这篇关于使用多个工作表对来自另一个工作簿的值求和的宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！