Add a cell from multiple spreadsheets

Posted on 2012-09-17
Last Modified: 2012-09-19

I have many spreadsheets all in the same folder on the network. Within each of the spreadsheet, there is a value in the same cell (B16), I would like a spreadsheet to be able add all the values from this cell from all the spreadsheets together and display the total.

How can this be done?
Question by:daiwhyte
    LVL 23

    Expert Comment




    where Start is first sheetname and End is last sheetname
    LVL 18

    Accepted Solution


    I guess the OP is talking about workbooks. If so, try

    Sub kTest()
        Dim i           As Long
        Dim fn          As String
        Dim MyFolder    As String
        Dim MySum       As Double
        Dim MyVal       As String
        MyFolder = "C:\Temp"  'adjust to suit
        If Right(MyFolder, 1) <> "\" Then MyFolder = MyFolder & "\"
        fn = Dir(MyFolder & "*.xls*")
        Do While fn <> vbNullString
            If fn <> ThisWorkbook.Name Then
                MyVal = "'" & MyFolder & "[" & fn & "]Sheet1'!R16C2"
                MySum = MySum + ExecuteExcel4Macro(MyVal)
            End If
            fn = Dir()
        MsgBox MySum
        'Range("B2") = MySum
    End Sub

    Open in new window


    Author Closing Comment

    thank you

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now