automate summing formula across 50 sheets

i have sheets like the attached. i need to put a formula in all the sheets

they are named in this format CA001, CA002...CA050

I need to sum the following in each book

row 8 - H:S, T:AE, AF:AQ,AR:BC,BD:BO, BP:CA
row 18- H:S, T:AE, AF:AQ,AR:BC,BD:BO, BP:CA
row 54 - H:S, T:AE, AF:AQ,AR:BC,BD:BO, BP:CA

Ideally, the sums of each could be put in a new sheet that would look like this
               ROW8           ROW18              ROW54
001-
002-

OR if much easier, just put the sums of each row in rows 55-57 within each sheet that its calculated
finnstoneAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
Here's a method that I have used for years, info taken from my web site

Summarise data from several Excel Worksheets…

If you have data on several sheets and need to create a summary then this example demonstrates a simple way to  create a summary sheet of the data from those sheets.

Note: The layout of the data must be exactly the same on each sheet, i. e corresponding amounts should always be in the same cell on each sheet.

For example a chain of shops has a sheet for each shop that records sales.. These sheets should be collected into one workbook, that in addition has a sheet with the same layout to hold the summary. You could use a formula like:

 =Sheet1!B2 +Sheet2!B2 + Sheet3!B8

However, this formula will need updating whenever a sheet is added or removed. My example demonstrates a simple way to summarise the data that will not involve having to change formulas if you change the number of data sheets. To make this trick work you need to add two sheets, one between the summary sheet and the the first data sheet and the other sheet should be to the right of the last data sheet. In the example I have named them Top and Bottom. In the summary sheet you add formulas to sum the corresponding data like:

=SUM(Top:Bottom!B2)

In the formula Top & Bottom are the outside sheet names. So this formula will add up the amounts on each sheet between these sheets that is in B2. If you use this method to record monthly data then you can add sheets as required. Providing that you use the same layout and place the new sheet between “Top” & “Bottom” then the data will update without any changes to the formula. By moving a sheet outside of the Top & Bottom tabs, that sheet's data is no longer included in the Summary sheet.

In Excel you can move the position of a sheet by simply clicking on the relevant Worksheet tab and dragging it to a different position. This makes this trick even more useful because you can see what would happen to the summary if you move a data sheet from between between the Top & Bottom tabs. This data is then removed from the totals, so in the simple example you could see the effect of closing one of the group’s branches.

I have added a macro that adds a sheet in the specified position between Top  & Bottom. This sheet is based on a hidden Template sheet.in the workbook. The user is prompted by a Excel VBA MessageBox to provide a new name for the record sheet that is added.

VIDEO
royUK-Summarise-Data-Tip.zip
0
finnstoneAuthor Commented:
thanks Roy, but not what I am looking for.

looking for others to weight in, thanks
0
NorieAnalyst Assistant Commented:
There's no attachment but based on what you posted you could try something like this.
Sub GetTotals()
Dim wsTotals As Worksheet
Dim strShName As String
Dim I As Long

    Set wsTotals = Sheets.Add
    
     wsTotals.Range("B1:D1").Value = Array("ROW8", "ROW18", "ROW54")
    For I = 1 To 50
        strShName = "CA" & Format(I, "000")
        With wsTotals
            .Cells(I + 1, 1).Value = strShName
            .Cells(I + 1, 2).Formula = "=SUM(" & strShName & "!H8:S8," & strShName & "!T8:AE8," & strShName & "!AF8:AQ8," & strShName & "!AR8:BC8," & strShName & "!BD8:BO8," & strShName & "!BP8:CA8)"
            .Cells(I + 1, 3).Formula = "=SUM(" & strShName & "!H18:S18," & strShName & "!T18:AE18," & strShName & "!AF18:AQ18," & strShName & "!AR18:BC18," & strShName & "!BD18:BO18," & strShName & "!BP18:CA18)"
            .Cells(I + 1, 4).Formula = "=SUM(" & strShName & "!H54:S54," & strShName & "!T54:AE54," & strShName & "!AF54:AQ54," & strShName & "!AR54:BC54," & strShName & "!BD54:BO54," & strShName & "!BP54:CA54)"
            
        End With
    Next I
    
End Sub

Open in new window


By the way, are you sure about the ranges you wanted to sum?

I'm asking because all the columns in them are contiguous so something like this,

=SUM('CA001'!H8:S8,'CA001'!T8:AE8,'CA001'!AF8:AQ8,'CA001'!AR8:BC8,'CA001'!BD8:BO8,'CA001'!BP8:CA8)

could be replaced with this.

=SUM('CA001'!H8:CA8)
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

finnstoneAuthor Commented:
Hi Norie, thanks! Here is the attachment (just one of the tabs - they all are in this format)

Rob
Book1.xlsx
0
finnstoneAuthor Commented:
i do not want a contiguous sum ,as each sum is a value i am seeking
0
NorieAnalyst Assistant Commented:
Rob

Do you have a workbook with multiple sheets formatted in this way?

Or is it separate workbooks each with a single sheet with this format?

Also, do you want to sum the ranges H8:S8, T8:AE8, AF8:AQ8 separately?
0
finnstoneAuthor Commented:
i tried your macro, it tries to open 50 files for some reason
0
finnstoneAuthor Commented:
yes sorry i was not clear, multiple sheets formatted this way in that same name format CA001..CA050

yes separately
0
NorieAnalyst Assistant Commented:
Rob

The code I posted would expect to find 50 sheets name CA001, CA002 etc. all in the same workbook.

If the sheets are in separate workbooks a different approach will be needed.
0
Roy CoxGroup Finance ManagerCommented:
Attach an example file. I would have thought my method would suit because it is flexible.

thanks Roy, but not what I am looking for.
doesn't explain what is missing from the solution.
0
finnstoneAuthor Commented:
my bad Norie. I did not assemble this book...i realize it was missing certain numbered sheets, like 17, 22, 35. your code works!
0
finnstoneAuthor Commented:
can you make one modficiation. see attached. i need this as output.
Book1.xlsx
0
NorieAnalyst Assistant Commented:
If there are missing workbooks we can add code to skip them.
Sub GetTotals()
Dim wsTotals As Worksheet
Dim strShName As String
Dim I As Long

    Set wsTotals = Sheets.Add
    
     wsTotals.Range("B1:D1").Value = Array("ROW8", "ROW18", "ROW54")
    For I = 1 To 50
        strShName = "CA" & Format(I, "000")
        
        
        With wsTotals
            
            .Cells(I + 1, 1).Value = strShName
            If Evaluate("ISREF('" & strShName & "'!A1)") Then
                .Cells(I + 1, 2).Formula = "=SUM(" & strShName & "!H8:S8," & strShName & "!T8:AE8," & strShName & "!AF8:AQ8," & strShName & "!AR8:BC8," & strShName & "!BD8:BO8," & strShName & "!BP8:CA8)"
                .Cells(I + 1, 3).Formula = "=SUM(" & strShName & "!H18:S18," & strShName & "!T18:AE18," & strShName & "!AF18:AQ18," & strShName & "!AR18:BC18," & strShName & "!BD18:BO18," & strShName & "!BP18:CA18)"
                .Cells(I + 1, 4).Formula = "=SUM(" & strShName & "!H54:S54," & strShName & "!T54:AE54," & strShName & "!AF54:AQ54," & strShName & "!AR54:BC54," & strShName & "!BD54:BO54," & strShName & "!BP54:CA54)"
            Else
                .Cells(I + 1, 2).Resize(, 3).Value = "#N/A"
            End If
        End With
    Next I
    
End Sub

Open in new window

0
finnstoneAuthor Commented:
yup that works better. did you get my prior message on getting the individual sums per sheet?
0
NorieAnalyst Assistant Commented:
Try this, it doesn't do the headings, that can be fixed, but it should do the formulas properly.
Sub GetTotals()
Dim wsTotals As Worksheet
Dim strShName As String
Dim I As Long
Dim J As Long
Dim K As Long
Dim col As Long
Dim arrCols As Variant
Dim arrRows As Variant

    Set wsTotals = Sheets.Add

    wsTotals.Range("B1:D1").Value = Array("ROW8", "ROW18", "ROW54")

    arrCols = Array("HX:SX", "TX:AEX", "AFX:AQX", "ARX:BCX", "BDX:BOX", "BPX:CAX")
    arrRows = Array(8, 18, 54)

    For I = 1 To 50
        strShName = "CA" & Format(I, "000")
        col = 2
        
        With wsTotals
            .Cells(I + 2, 1).Value = strShName
            If Evaluate("ISREF('" & strShName & "'!A1)") Then
                For J = LBound(arrCols) To UBound(arrCols)
                    For K = LBound(arrRows) To UBound(arrRows)
                        .Cells(I + 2, col).Formula = "=SUM(" & strShName & "!" & Replace(arrCols(J), "X", arrRows(K)) & ")"
                        col = col + 1
                    Next K
                Next J
                
                Else
                .Cells(I + 2, 2).Resize(, 3).Value = "#N/A"
                End If
        End With
    Next I

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
finnstoneAuthor Commented:
ok i am creating a part 2 if you are free. now i need to do the same thing, but set the array values for each sheet based on the value in col A

so array does not equal 8,18,54 for each sheet ...instead x,yx for each sheet are based on the following

x = where in col A this value exisits, "Total Income"
y = where in col A this value exisits, "Gross Profit"
z = where in col A this value exisits, "Net Income"

i will link to the new question in 2 hours, need to wait now for it to go live, much appreciated if you can work on it here :)
0
finnstoneAuthor Commented:
need part 2 because i realized every sheet is not the same length in rows so revenue , profit and net income are in different rows acorss the tabs
0
NorieAnalyst Assistant Commented:
Thought that might be the case when I looked at the sample sheet.

Should be straightforward though, just replace the row values in the array with the row 'titles' and then use Match to find the appropriate row number for each title on each sheet.
0
finnstoneAuthor Commented:
nice, thanks, can you send the code for me? i will send you a 2nd question shortly and award you there
0
NorieAnalyst Assistant Commented:
Kind of busy right now, I'll keep an eye out for the 2nd question and post there.
0
finnstoneAuthor Commented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.