Excel - Calculation acorss multiple sheets

[superclass12]

I am completely lost on how to go about VBA/Macro syntax on the calculation I would like to obtain from multiple sheets. So your help would be greatly appreciated.

I have, let's say, 50 investments, all with individual sheets. Thus, sheets are 1 through 50. On cell A1 of each sheet, I have % value which signify each investment's return.

On a completely separate sheet called, "Allocations", I have the list of investments (50 investments) with how much % is allocated to each investment out of the entire portfolio. (Let's say the % values are on B1 to B50)

On "Summary" sheet, I would like to find out the following:
=1!A1 * Allocations!B1 + 2!A1 * Allocations!B2 + 3!A1 * Allocations!B3 + etc.

Is there easier way to do this without repeating the formula 50 times or more?

Thanks.

 

[Jerry Sullivan]

Welcome to the Board!

If your sheet is setup exactly as you describe, you could use the code below.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub SumAllocations()
    Dim dblTotal As Double
    Dim i As Long
    On Error GoTo ErrorHandler
    With Sheets("Allocations")
        For i = 1 To 50
            dblTotal = dblTotal + _
             (Sheets(CStr(i)).Range("A1") * .Cells(i, "B"))
        Next i
    End With
    Sheets("Summary").Range("A1") = dblTotal
    Exit Sub
ErrorHandler:
    Sheets("Summary").Range("A1").ClearContents
    MsgBox "Error calculating Sheet: " & i & " subtotal.", _
       vbExclamation, "Error"
End Sub

Once you get this working you might want to consider a couple of improvements that will make it more adaptable to changes in your workbook.
1. Instead of having to hardcode the number of sheets into the VBA code, revise the code to process each worksheet in the workbook except Summary, Allocations, etc...

2. Instead of relying on each sheet's allocation to be on the corresponding Row of Sheet Allocations Column B, you could have VBA lookup the Sheet name in a table and return the allocation value for that sheet. This is kind of what you are doing now except the lookup approach would allow you to move the table from Row 1 and also allow you to use any Sheet Names instead of only numbers.

Good luck!

 

[superclass12]

Jerry,
Thanks so much for the guidance - it works perfectly well! As you suggested, I will try to work on the revisions to 'improve' it a bit.

I have been going through many posts in MrExcel.com and I truly love it!