|
|||||||||
|
|||||||||
|
||||||||||||||||||||||||||||
» Add cells in a range from all worksheets in the workbook using VBA in Microsoft Excel
 | ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
|
It's possible to achieve this with the built-in SUM()-function, but that can be a big task if the workbook contains many worksheets, or if you frequently add new worksheets to the workbook. To add a range from several worksheets you can use the SUM-function like this: =SUM(Sheet1:Sheet10!A1:A100) The formula above will add the range A1:A100 in the worksheets Sheet1 and Sheet10, including all worksheets between the two sheets in the workbook. With the custom function below it's easy to add a range from all the worksheets in a workbook:
Function SumAllWorksheets(InputRange As Range, InclAWS As Boolean) As Double
' adds the content of InputRange in all worksheets in the workbook
Dim ws As Worksheet, TempSum As Double
Application.Volatile True ' calculates every time a cell changes
TempSum = 0
For Each ws In ActiveWorkbook.Worksheets
If InclAWS Then ' include the active worksheet
TempSum = TempSum + _
Application.WorksheetFunction.Sum(ws.Range(InputRange.Address))
Else ' exclude the active worksheet
If ws.Name <> ActiveSheet.Name Then ' not the active worksheet
TempSum = TempSum + _
Application.WorksheetFunction.Sum(ws.Range(InputRange.Address))
End If
End If
Next ws
Set ws = Nothing
SumAllWorksheets = TempSum
End Function
The function is used in the same way as the built-in worksheetfunctions. In Excel 5/95 the variable InputRange must be defined as Variant instead of Range. The function can easily be adapted for use with other worksheetfunctions that you want to use on all the worksheets in a workbook. | ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
|
READER COMMENTS (view all comments) |
||||||||||||||||
|
No comments have been submitted. | ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
 | ||||||||||||||||
Windows XP Annoyances
The McGraw-Hill 36-Hour Course in Finance for Nonfinancial Managers
Managerial Accounting: Tools for Business Decision Making, WebCT, 2nd Edition
Marketing Plans That Work, Targeting Growth and Profitability
Marketing Plan: A Handbook with Marketing Plan
How to Read A Financial Report | ||||||||||||||||
| ||||||||||||||||
RELATED MICROSOFT EXCEL TIPS
 |
• Excel VBA books
• Accounting books
• Business Plans
• MS Office books
• Taxes books
|
Terms
and Conditions of use |
|
|
Copyright © 2003 ExcelTip.com |
|