VBA Code to Sum Same Cell from Multiple Sheets

[KhanofTarkir]

Hello, I am just starting to learn VBA to do some sales analyses.

Does anyone have VBA code that I could use in a module to sum all the values from the same cell in multiple sheets (i.e. cell C16 in US, EU, Canada sales worksheets) into a final master sheet?

I am guessing that the code would require me to enter in the number and names of the sheets I want to sum from, the cell that I want to sum, and the destination master sheet with the final sum.

I am very new to VBA so any help would be greatly appreciated!

Thank you.

 

[NeonRedSharpie]

Function ADDACROSSSHEETS(rng As Range) As Variant

    valRow = rng.Row
    valCol = rng.Column


    For x = 1 To Sheets.Count
        ADDACROSSSHEETS = Sheets(x).Cells(valRow, valCol).Value + ADDACROSSSHEETS
    Next x


End Function

I'm not sure if you wanted it as a function or as part of a sub, so here it is as a function. You could then call this within a sub if you need.

 

[KhanofTarkir]

Thank you very much for the code!

Just to clarify, if I want to sum cell C16 from sheets 1 through 5 into my master sheet on sheet 6, how would I modify the code?

Would it be:

Code:

Function ADDACROSSSHEETS(rng As Range) As Variant

valRow = 16
valCol = C
nonmastersheets = Sheets.count - 1

For x = 1 To nonmastersheets

ADDACROSSSHEETS = Sheets(x).Cells(valRow, valCol).Value + ADDACROSSSHEETS

Next x

End Function

 

[NeonRedSharpie]

So the "rng as range" is how you get the cell to add. So this function can be used in the workbook. If you don't want it to be a function, it would be a code snippet of:

    valRow = 16
    valCol = 3


    For x = 1 To Sheets.Count
        ADDACROSSSHEETS = Sheets(x).Cells(valRow, valCol).Value + ADDACROSSSHEETS
    Next x

 

[KhanofTarkir]

Ah I see now, that makes things much more clear!

 

[KhanofTarkir]

Since I will likely need to circulate this vba among my peers, is it possible to code in the function in a more user friendly format with the function as follows?

Function would be: ADDACROSSSHEETS([row number], [column number], [starting sheet for adding], [last sheet for adding])

My VBA knowledge is very basic so I am unsure if this additional functionality is possible.. thank you

 

[NeonRedSharpie]

Sure, a few clarifying points:

Is this being used in a module or on the workbook
Will the sheets always be the same
How tech savvy are the people using the function

 

[KhanofTarkir]

Hi NeonRedSharpie,

1) I believe that it will be used in a module.

2) The sheets will not always be the same and the number of sheets will likely be different across several workbooks. Therefore, I was thinking that if the function could specify which sheet to start and end the summation it would be useful, either by explicit sheet name or just sheet number. I also emphasize that summations will not take place across sheets in entirely separate workbooks, summations will only occur among sheets in a single workbook.

3) On a scale of 1 to 10 with 10 being an excel ninja I would say the people using this function would be a 3?

Thanks you!

 

[NeonRedSharpie]

So for point #2, the only concern is if the sheets are always going to be in sequential order. If they aren't, then I'd have to have an array passed to it and that just adds a lot more questions. Assuming they're in sequential order, the code would be:

Function ADDACROSSSHEETS(valRow As Long, valCol As Long, _          
     sheetStart As Integer, sheetEnd As Integer) As Variant


    For x = sheetStart To sheetEnd
        ADDACROSSSHEETS = Sheets(x).Cells(valRow, _
            valCol).Value + ADDACROSSSHEETS
    Next x


End Function

 

[KhanofTarkir]

Yes that's exactly what I was looking for - the code works perfectly in the master worksheet.

I believe the sheets will always be in sequential order since there may be some "analysis sheets" before or after that block of "raw data" sheets I want to sum from.

Thank you very much!