Need Macro to Sum multiple tabs based on cell value

[cSciFiChick]

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.

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!

 

[cSciFiChick]

I wish that VBA made more sense to me so I could do these on my own

 

[Eric W]

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:

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

Book1 (version 1).xlsb
	A	B	C	D	E
1
2
3
4
5		Description	Material	Labor	totals
6		CPU	10	58	68
7		Tower	12	71	83
8		Monitors	14	84	98
9		Misc	16	97	113
10		Admin	18	111	129
Sheet11

Cell Formulas
Range	Formula
C6:E10	C6	=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]

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
	A	B	C	D	E
1
2
3
4
5		Description	Material	Labor	totals
6		CPU	10	58	68
7		Tower	12	71	83
8		Monitors	14	84	98
9		Misc	16	97	113
10		Admin	18	111	129
Sheet11

Cell Formulas
Range	Formula
C6:E10	C6	=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]

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:

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
	A	B	C	D	E
1
2
3
4
5		Description	Material	Labor	totals
6		CPU	10	58	68
7		Tower	12	71	83
8		Monitors	14	84	98
9		Misc	16	97	113
10		Admin	18	111	129
11
Sheet11

Cell Formulas
Range	Formula
C6:E10	C6	=sumapproved(C6)

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