Calculate values across the sheets in a workbook.

L

llbac

New Member
Joined
Jul 20, 2023
Messages
30
Office Version
  1. 365
Platform
  1. Windows
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:
Excel Formula: 
=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
AB
1Title ATitle B
20.5375522780.3560159
30.7280599090.0117653
40.2319460940.8570487
50.3699685550.7705613
60.4512323070.9802114
SheetA

SheetB:
Fomular-test-3.xlsm
AB
1Title ATitle B
20.8014033280.398481
30.6547618920.415879
40.8878485630.7809355
50.0966875590.1760872
60.8971794860.115832
SheetB

SheetC:
Fomular-test-3.xlsm
AB
1Title ATitle B
20.933571770.5453227
30.4280931250.8806354
40.2195009150.6875302
50.3107672140.3542
60.0383726370.9443508
SheetC

The sheet Master I want to calculate from all other sheets:
Fomular-test-3.xlsm
A
1Across
21.019819025
30.657861573
41.043055101
50.41218262
60.582462382
Master
Cell Formulas
RangeFormula
A2:A6A2=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!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
There are two names in Excel VBA, the displayed name (RED) and the "code name" (GREEN) name, see picture below.

Excel_Sheet_Names.png

You could work with that, but keep one thing in mind:
The "internal" name will be created and numbered automatically with each new sheet like this:

New workbook created will have both the same "Sheet1"
New worksheet will be "Sheet2" and the "code name" name won't change (unless you do so)
-> When you change the "displayed" name to "NameX", "NameY" and "NameZ", the "code name" names will still be "Sheet1", "Sheet2" and "Sheet3" in order of creation.

There's a good tutorial on Excel Sheet Names here automateexcel.com and automateexcel.com

If the numer of sheets you want to calculate is the same as the number of sheets available in your workbook minus the Master Sheet:
-> 3 Sheets plus 1 Master Sheet: "Master", "SheetA", "SheetB", "SheetC"

Then you need to look up the "Code Name" of your "Master" Sheet and the other sheets and see if their naming is "continuous"

If it is, you're ready to go.
-> e.g. "Master" = "Sheet1", "SheetA" = "Sheet2", "SheetB" = "Sheet3", "SheetC" = "Sheet4", etc.

Then your code would look like this:
VBA Code: 
Sheet2.Range ("A2") * Sheet2.Range("B2") + Sheet3.Range("A2") * Sheet3.Range("B2") + Sheet4.Range("A2") * Sheet4.Range("B2")
etc.

But with a big number of sheets your code will be very long and you should consider to "loop through" your sheets rather then addressing one by one.

In Order to get a VBA code I need a few more information on your data or input:
How many rows are there per sheet? Is it always row 2 to row 6?

Hope it helps a little bit.
Please tell me more about your data.
 
Upvote 0
Upvote 0
@llbac Assuming that the sheet 'Master' exists then, maybe like below with vba.

VBA Code: 
Sub llbac()
Dim arr As Variant, res As Variant
Dim lr As Long, mlr As Long

For Each sht In ThisWorkbook.Sheets
    If Not sht.Name = "Master" Then
        If Not lr > 1 Then
            lr = sht.Cells(Rows.Count, 1).End(xlUp).Row
            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
End Sub
Hope that helps.
 
Upvote 0
PeteWright said:
There are two names in Excel VBA, the displayed name (RED) and the "code name" (GREEN) name, see picture below.

View attachment 95780
You could work with that, but keep one thing in mind:
The "internal" name will be created and numbered automatically with each new sheet like this:

New workbook created will have both the same "Sheet1"
New worksheet will be "Sheet2" and the "code name" name won't change (unless you do so)
-> When you change the "displayed" name to "NameX", "NameY" and "NameZ", the "code name" names will still be "Sheet1", "Sheet2" and "Sheet3" in order of creation.

There's a good tutorial on Excel Sheet Names here automateexcel.com and automateexcel.com

If the numer of sheets you want to calculate is the same as the number of sheets available in your workbook minus the Master Sheet:
-> 3 Sheets plus 1 Master Sheet: "Master", "SheetA", "SheetB", "SheetC"

Then you need to look up the "Code Name" of your "Master" Sheet and the other sheets and see if their naming is "continuous"

If it is, you're ready to go.
-> e.g. "Master" = "Sheet1", "SheetA" = "Sheet2", "SheetB" = "Sheet3", "SheetC" = "Sheet4", etc.

Then your code would look like this:
VBA Code: 
Sheet2.Range ("A2") * Sheet2.Range("B2") + Sheet3.Range("A2") * Sheet3.Range("B2") + Sheet4.Range("A2") * Sheet4.Range("B2")
etc.

But with a big number of sheets your code will be very long and you should consider to "loop through" your sheets rather then addressing one by one.

In Order to get a VBA code I need a few more information on your data or input:
How many rows are there per sheet? Is it always row 2 to row 6?

Hope it helps a little bit.
Please tell me more about your data.
Click to expand...
Yes, "loop through" is exactly what I supposed to do with VBA, because the number of sheets varies each case, usually more than 30.
The number of rows is also different each case, so it is hard to tell a specific number, perhaps about 60 rows, more or less.

I hope I made it clear.
 
Upvote 0
keda duck said:
textt.xlsx
ABCD
1acrossSheetA
21.0198190251.019819025SheetB
30.6578615730.657861573SheetC
41.0430551011.043055101
50.412182620.41218262
60.5824623820.582462382
master
Cell Formulas
RangeFormula
A2:A6A2=SheetA!A2*SheetA!B2+SheetB!A2*SheetB!B2+SheetC!A2*SheetC!B2
B2:B6B2=SUM(REDUCE({""},$D$1:$D$3,LAMBDA(x,y,VSTACK(x,INDIRECT(y&"!r"&ROW()&"c1",)*INDIRECT(y&"!r"&ROW()&"c2",)))))

In this solution, you should write the sheet name in column d.
Click to expand...
Unfortunately, I am afraid of that typing more than 30 sheets, which their names are long and random, will be a bottleneck in my workflow if any typing errors occur. Also, I have a lot of workbooks to repeat this calculation again and again.
I suppose that the best approach for me is to cycle through all the sheets automatically without typing their names.
Anyway, I really appreciate your kind help!
 
Upvote 0
Snakehips said:
@llbac Assuming that the sheet 'Master' exists then, maybe like below with vba.

VBA Code: 
Sub llbac()
Dim arr As Variant, res As Variant
Dim lr As Long, mlr As Long

For Each sht In ThisWorkbook.Sheets
    If Not sht.Name = "Master" Then
        If Not lr > 1 Then
            lr = sht.Cells(Rows.Count, 1).End(xlUp).Row
            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
End Sub
Hope that helps.
Click to expand...
I got error '9': Subscript out of range at line:
VBA Code: 
ReDim arr(1 To lr - 1, 1 To 1)
Could you please tell me how to solve this?
 
Upvote 0
llbac said:
Unfortunately, I am afraid of that typing more than 30 sheets, which their names are long and random, will be a bottleneck in my workflow if any typing errors occur. Also, I have a lot of workbooks to repeat this calculation again and again.
I suppose that the best approach for me is to cycle through all the sheets automatically without typing their names.
Anyway, I really appreciate your kind help!
Click to expand...
If you want to write sheet name dynamically, a common approach is used name manager of formula in the menu bar. Create a name which is sheetname, its formula is =GET.WORKBOOK(1)&T(NOW()). You should put Master on the first one, and then you can use the formula of this mini-sheet.

textt.xlsx
ABCD
1acrossSheetA
21.0198190251.019819025SheetB
30.6578615730.657861573SheetC
41.0430551011.043055101
50.412182620.41218262
60.5824623820.582462382
master
Cell Formulas
RangeFormula
D1:D3D1=TRANSPOSE(DROP(MID(sheetname,FIND("]",sheetname)+1,99),,1))
A2:A6A2=SheetA!A2*SheetA!B2+SheetB!A2*SheetB!B2+SheetC!A2*SheetC!B2
B2:B6B2=SUM(REDUCE({""},D$1#,LAMBDA(x,y,VSTACK(x,INDIRECT(y&"!r"&ROW()&"c1",)*INDIRECT(y&"!r"&ROW()&"c2",)))))
Dynamic array formulas.
 
Last edited:
Upvote 0
keda duck said:
If you want to write sheet name dynamically, a common approach is used name manager of formula of menu. Create a name which is sheetname, its formula is =GET.WORKBOOK(1)&T(NOW()). You should put Master on the first one, and then you can use the formula of this mini-sheet.

textt.xlsx
ABCD
1acrossSheetA
21.0198190251.019819025SheetB
30.6578615730.657861573SheetC
41.0430551011.043055101
50.412182620.41218262
60.5824623820.582462382
master
Cell Formulas
RangeFormula
D1:D3D1=TRANSPOSE(DROP(MID(sheetname,FIND("]",sheetname)+1,99),,1))
A2:A6A2=SheetA!A2*SheetA!B2+SheetB!A2*SheetB!B2+SheetC!A2*SheetC!B2
B2:B6B2=SUM(REDUCE({""},D$1#,LAMBDA(x,y,VSTACK(x,INDIRECT(y&"!r"&ROW()&"c1",)*INDIRECT(y&"!r"&ROW()&"c2",)))))
Dynamic array formulas.
Click to expand...
I got error 'NAME' at formular at D column:
CR28A_NMR_test-7-Final.xlsm
D
1#NAME?
Sheet1
Cell Formulas
RangeFormula
D1D1=TRANSPOSE(DROP(MID(sheetname,FIND("]",sheetname)+1,99),,-1))


Could please give me any further instruction?
 
Upvote 0
@llbac That indicates to me that the row number for the last row of data, lr, is being computed as 1.
If so then the code will error when it attempts to dim the arrays with lr-1.
The code assumes that lr can be determined by the last row of data in column A of any sheet other than the Master sheet.
So it does this using the first data sheet it finds.

I have tweaked the code, below, so that in the case of such an error it will fail in a more controlled manner.
However, why is it failing?
Here are my interpretation of your original post.
A variable number of randomly named sheets all contain similarly structured data in columns A & B.
There is one other non-data sheet that is named 'Master' (Edit code if this sheet name differs) .
Sheet 'Master' is to receive results.
If any of the above is not correct then please advise as the code will need to reflect your reality.

VBA Code: 
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, 4).End(xlUp).Row      '<<< 1 = column A  Edit for another column if A not applicable
            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
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Formula returns array rather than value
Replies
1
Views
361
dreid1011
dreid1011
C
  • Solved
Copy data from multiple sheets into a master sheet - VBA button stuck
Replies
3
Views
178
NdNoviceHlp
NdNoviceHlp
S
  • Solved
Dynamic pulldown of sheet names
Replies
2
Views
129
ShadowRider
S
S
  • Solved
VBA Code for lookup with multiple criteria
Replies
7
Views
423
kvsrinivasamurthy
K
E
  • Question Question
Stop Duplicate Entries.
Replies
1
Views
44
mumps
mumps

Forum statistics

Threads
1,223,884
Messages
6,175,175
Members
452,615
Latest member
bogeys2birdies

Share this page

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
Back
Top