CODEWITHSUNDEEP
excelvba
bearded4glory
bearded4glory

Reputation: 1

Excel VBA Sum from Multiple Sheets

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.

Upvotes: 0

Views: 16459

Answers (3)

user6012628
user6012628

Reputation:

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 :

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152505

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.

Upvotes: 1

user1274820
user1274820

Reputation: 8144

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:

Results

Upvotes: 2

Related Questions