Excel VBA Sum from Multiple Sheets

Question

I am trying to create a function or functions that can sum daily hours from time cards for each client to come up with the total hours worked per day. Each client has it's own sheet inside of a single workbook.

Currently, I have a function that determines the sheet that goes with the first client (the third sheet in the workbook):

Function FirstSheet()
Application.Volatile
FirstSheet = Sheets(3).Name
End Function

And one to find the last sheet:

Function LastSheet()
Application.Volatile
LastSheet = Sheets(Sheets.Count).Name
End Function

The part that I am having trouble with it getting these to work within the sum function.

=sum(FirstSheet():LastSheet()!A1

That is basically what I want to accomplish. I think the problem is that I don't know how to concatenate it without turning it into a string and it doesn't realize that it is sheet and cell references.

Any help would be greatly appreciated.

Let me know if I answered your question - if you need more help, feel free to let me know. — user1274820
– user1274820, Commented Dec 7, 2016 at 23:03

user1274820 · Accepted Answer · 2016-12-07 23:00:21Z

2

So, an example formula would look like this:

=SUM(Sheet2!A1:A5,Sheet3!A1:A5,Sheet4!A1:A5)

That would sum Sheet2-Sheet4, A1:A5 on all sheets.

Is there a reason you need to write the VBA code to do this?

Can't you just enter it as a formula once?

Also, if you're going to the trouble of writing VBA to generate a formula, it may make more sense to just do the sum entirely in VBA code.

If not, try this:

Sub GenerateTheFormula()
Dim x, Formula
Formula = "=SUM(" 'Formula begins with =SUM(
For x = 3 To Sheets.Count
    Formula = Formula & Sheets(x).Name & "!A1," 'Add SheetName and Cell and Comma
Next x
Formula = Left(Formula, Len(Formula) - 1) & ")" 'Remove trailing comma and add parenthesis
Range("B1").Formula = Formula 'Where do you want to put this formula?
End Sub

Results:

edited Dec 7, 2016 at 23:00

answered Dec 7, 2016 at 22:51

user1274820

8,1894 gold badges39 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user6432984 Over a year ago

I like the gif. You might want to consider reducing the white space and showing the tabs. Compare yours to my demo where I resize the Excel window Demo

user1274820 Over a year ago

Haha well, thanks for the tip - I throw it together usually, but it's not a bad idea. I rarely have to show the sheet - I mostly just worry about keeping the gif size small because the file size is terrible.

user1274820 Over a year ago

Since you gave me a tip, I will give you one. Checkout ScreenToGif. It's free and super useful. screentogif.codeplex.com

user6432984 Over a year ago

Thanks but that's what I use. Do you have anything else for me?..lol

user1274820 Over a year ago

Ahh I should have known lol. Yes, eat your vegetables and brush your teeth. That is all ;)

Scott Craner · Accepted Answer · 2016-12-08 01:24:29Z

1

The functions return strings and not actual worksheets. The Worksheet does not parse strings well. So add a third function that uses the Evaluate function:

Function MySum(rng As Range)

MySum = Application.Caller.Parent.Evaluate("SUM(" & FirstSheet & ":" & LastSheet & "!" & rng.Address & ")")

End Function

Then you would simply call it: MySum(A1)

It uses the other two function you already have created to create a string that can be evaluated as a formula.

answered Dec 8, 2016 at 1:24

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

4 Comments

bearded4glory Over a year ago

This seems like it should work but I get a #VALUE! when I try to use it. I am in Excel 2016 if it makes a difference.

Scott Craner Over a year ago

It works on my Office 365 Excel. I am use windows. I am not sure about Excel 2016.

bearded4glory Over a year ago

I got it to work, not sure why it wasn't working initially but I ended up just rolling the other functions into this one since they were fairly short. Thanks!

Scott Craner Over a year ago

@bearded4glory you do that by clicking on the check mark by the answer.

user6012628 · Accepted Answer · 2016-12-08 04:22:19Z

I didn't understand ur question completely but As I understood u have different sheets of different clients which contains supoose column 1 date and column 2 contains hours on that particular date wise hours and a final sheet which column1 contains name of client and column 2 contains total hours
Please try it

Sub countHours()
Dim last_Row As Integer
Dim sum As Double
sum = 0
  'Because I know number of client
   For i = 1 To 2     'i shows client particular sheet

  last_Row = Range("A" & Rows.Count).End(xlUp).Row

  Sheets(i).Activate

  For j = 2 To last_Row

  'In my Excel sheet column 1 contains dates and column 2 contains number of hours

  sum = sum + Cells(j, 2)
  'MsgBox sum
  Next j

  'Sheet 3 is my final sheet
  ThisWorkbook.Sheets(3).Cells(i + 1, 2).Value = sum
  sum = 0
  Next i

  End Sub

Happy Coding :

Collectives™ on Stack Overflow

Excel VBA Sum from Multiple Sheets

3 Answers 3

5 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related