asked on

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

Roy Cox

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

finnstone

ASKER

thanks Roy, but not what I am looking for.

looking for others to weight in, thanks

Norie

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)

finnstone

ASKER

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

Rob
Book1.xlsx

finnstone

ASKER

i do not want a contiguous sum ,as each sum is a value i am seeking

Norie

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?

finnstone

ASKER

i tried your macro, it tries to open 50 files for some reason

finnstone

ASKER

yes sorry i was not clear, multiple sheets formatted this way in that same name format CA001..CA050

yes separately

Norie

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.

Roy Cox

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.

finnstone

ASKER

my bad Norie. I did not assemble this book...i realize it was missing certain numbered sheets, like 17, 22, 35. your code works!

finnstone

ASKER

can you make one modficiation. see attached. i need this as output.
Book1.xlsx

Norie

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

finnstone

ASKER

yup that works better. did you get my prior message on getting the individual sums per sheet?

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

finnstone

ASKER

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 :)

finnstone

ASKER

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

Norie

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.

finnstone

ASKER

nice, thanks, can you send the code for me? i will send you a 2nd question shortly and award you there

Norie

Kind of busy right now, I'll keep an eye out for the 2nd question and post there.

finnstone

ASKER

here it is

https://www.experts-exchange.com/questions/29090548/part-2-array.html