Need Macro to Sum multiple tabs based on cell value

cSciFiChick

New Member
Joined
Jul 31, 2014
Messages
34
So I have a worksheet that has multiple tabs with job quotes. New tabs are added all the time. So I would like a Summary tab where I can show the totals of quotes but only for the ones that have been accepted.

1657817623814.png


So for each tab in my workbook which the number of tabs changes. I want something where it looks in B2 and if it sees Approved then it will take the numbers in columns C-E and Sum every tab that has "Approved". The rows do not change they have the same codes and descriptions.

Can anyone please help me. I have tried to build my own using other VBA I have found but nothing quite fits what I need.

Thank you!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,490
You can actually do this without VBA, if you set up your sheet right. Add a sheet named Approved_Start, and another named Approved_End. Now move any Approved sheets in between those. (Just click and drag the sheet tab between them.) It'll look something like this:

1657839054168.png


Then you can use a 3-D formula like this:


Book1 (version 1).xlsb
ABCDE
1
2
3
4
5DescriptionMaterialLabortotals
6CPU105868
7Tower127183
8Monitors148498
9Misc1697113
10Admin18111129
Sheet11
Cell Formulas
RangeFormula
C6:E10C6=SUM(Approved_Start:Approved_End!C6)


If this doesn't work for you, let me know and I'll write up a UDF for you.
 

cSciFiChick

New Member
Joined
Jul 31, 2014
Messages
34
You can actually do this without VBA, if you set up your sheet right. Add a sheet named Approved_Start, and another named Approved_End. Now move any Approved sheets in between those. (Just click and drag the sheet tab between them.) It'll look something like this:

View attachment 69339

Then you can use a 3-D formula like this:


Book1 (version 1).xlsb
ABCDE
1
2
3
4
5DescriptionMaterialLabortotals
6CPU105868
7Tower127183
8Monitors148498
9Misc1697113
10Admin18111129
Sheet11
Cell Formulas
RangeFormula
C6:E10C6=SUM(Approved_Start:Approved_End!C6)


If this doesn't work for you, let me know and I'll write up a UDF for you.


My only problem is I have a Macro that automates adding new sheets and it is coded to always add to the new sheet after all the sheets. I am not sure how to code it to add it after the other sheets but before this approved_End one. Also I only want this to add up totals if the Cell B2 says approved. I am building this for multiple people to use at work and trying to make it as automated as possible I do not trust to have them moving around tabs etc. :(
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,490
Well, it was worth mentioning.

Open a COPY of your workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. Copy the following code and paste it into the window that opens:

VBA Code:
Public Function SumApproved(target As Range)

    For Each s In Worksheets
        If s.Range("B2").Value = "Approved" And InStr(target.Address(, , , 1), s.Name) = 0 Then
            SumApproved = SumApproved + s.Range(target.Address)
        End If
    Next s
    
End Function

Press Alt-Q to close the VBA editor. Now you can use this function like this:

Book1 (version 1).xlsb
ABCDE
1
2
3
4
5DescriptionMaterialLabortotals
6CPU105868
7Tower127183
8Monitors148498
9Misc1697113
10Admin18111129
11
Sheet11
Cell Formulas
RangeFormula
C6:E10C6=sumapproved(C6)


I specifically made it so it ignores the current sheet. Let me know how this works.
 

Forum statistics

Threads
1,175,530
Messages
5,897,952
Members
434,688
Latest member
vi28

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
Top