Calculate values across the sheets in a workbook.

[llbac]

Hi everyone,

Could anyone please tell me how to write a VBA to do this simple task (but not easy for me ^^!, sadly)?
I have a workbook that contains many sheets (maybe 30 sheets or more) which their names are quite random.
Then I will create a sheet named Master and calculate very simple formular like this:

=SheetA!A2*SheetA!B2+SheetB!A2*SheetB!B2+SheetC!A2*SheetC!B2..........

That is the sum of all the multiplication of two cells of two column (A & B) on the same row (e.g. A2 * B2), for all of the sheets available on the workbook.
All the sheets I want to calculate have the same structure: column A and B with number, start from row 2. Also, the number of rows is the same in every sheets.
This is the samples in XL2BB:
SheetA:

Fomular-test-3.xlsm
	A	B
1	Title A	Title B
2	0.537552278	0.3560159
3	0.728059909	0.0117653
4	0.231946094	0.8570487
5	0.369968555	0.7705613
6	0.451232307	0.9802114
SheetA

SheetB:

Fomular-test-3.xlsm
	A	B
1	Title A	Title B
2	0.801403328	0.398481
3	0.654761892	0.415879
4	0.887848563	0.7809355
5	0.096687559	0.1760872
6	0.897179486	0.115832
SheetB

SheetC:

Fomular-test-3.xlsm
	A	B
1	Title A	Title B
2	0.93357177	0.5453227
3	0.428093125	0.8806354
4	0.219500915	0.6875302
5	0.310767214	0.3542
6	0.038372637	0.9443508
SheetC

The sheet Master I want to calculate from all other sheets:

Fomular-test-3.xlsm
	A
1	Across
2	1.019819025
3	0.657861573
4	1.043055101
5	0.41218262
6	0.582462382
Master

Cell Formulas
Range	Formula
A2:A6	A2	=SheetA!A2*SheetA!B2+SheetB!A2*SheetB!B2+SheetC!A2*SheetC!B2

The structure is simple but when typing for too many sheets, usually mistakes happened then it will be a huge problem for my final result.
The point here is that:
The code should calculate all available sheets without knowing sheets' names, and paste to the current active sheet (e.g. Master).
Really appreciate your help!

 

[Snakehips]

@llbac Have PM'd you with a link to my test file.

 

[llbac]

I'm a bit baffled.
Running that code on that data here resets in the below result

Book3
	A
1	My Result
2	162.974184
3	164.146768
4	183.354493
5	56.941502
6	67.3467336
7	65.3640183
8	-3.2633293
9
Master

Can you add a line to message the value you are getting for lr and see what it is?
It should of course equal the last row of your data.

Sub llbac ()
Dim arr As Variant, res As Variant
Dim lr As Long, mlr As Long, c As Long
Dim sht As Worksheet

For Each sht In ThisWorkbook.Sheets
    If Not sht.Name = "Master" Then  '<<< Edit Master if name differs
        If Not lr > 1 Then
        On Error GoTo BailOut  'controled exit sub without failing
            lr = sht.Cells(Rows.Count, 1).End(xlUp).Row      '<<< 1 = column A  Edit for another column if A not applicable

MsgBox lr  '**for diagnostics

            If Not lr > 1 Then MsgBox "Check that your first data sheet has data rows in column A"   '<<<<check if lr is being set correctly??????"
            ' if no data or only header row lr <= 1 then the following will error
            ReDim arr(1 To lr - 1, 1 To 1)
            ReDim res(1 To lr - 1)
        End If
    arr = sht.Range("A2:B" & lr).Value
        For c = 1 To lr - 1
            res(c) = res(c) + (arr(c, 1) * arr(c, 2))
        Next c
    End If
Next sht
With Sheets("Master")
    mlr = .Cells(Rows.Count, 1).End(xlUp).Row
    .Range("A2:A" & mlr).ClearContents
    .Range("A2:A" & lr).Value = Application.Transpose(res)
End With
BailOut:
On Error GoTo 0  'reset default error handling
End Sub

Finally, I know the reason the code didn't work. It is because I saved the macro in my Personal.XLSB
When I copied and pasted macro to create new macro in the current file, it worked!
I have looked up this issue over the internet, then I know the solution is replacing this line in VBA Code:

ThisWorkbook.ActiveSheet

WITH
VBA Code:

ActiveWorkbook.ActiveSheet

Then it worked fine from Personal.XLSB

I really appreciate your kind help!
It should be note that the function solutions of @Fluff and @keda duck also works and I am really grateful for your supports!
Best regards!