You are currently offline, waiting for your internet to reconnect

PRJ: Macro to Sum Values in Filtered List

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

This article was previously published under Q124482
Summary
The Microsoft Project sample macro in the "More Information" section ofthis article sums numeric values from a list of visible tasks and displaysthe total in a task row at the bottom of the list.
More information
Microsoft provides examples of Visual Basic for Applications procedures forillustration only, without warranty either expressed or implied, including,but not limited to the implied warranties of merchantability and/or fitnessfor a particular purpose. The Visual Basic procedures in this article areprovided 'as is' and Microsoft does not guarantee that they can be used inall situations. While Microsoft Support professionals can help explain thefunctionality of a particular macro, they will not modify these examples toprovide added functionality, nor will they help you construct macros tomeet your specific needs. If you have limited programming experience, youmay want to consult one of the Microsoft Solution Providers. SolutionProviders offer a wide range of fee-based services, including creatingcustom macros. For more information about Microsoft Solution Providers,call Microsoft Customer Information Service at (800) 426-9400.

The following macro sums the values from the Number1 fields of all visibletasks. After you run this macro, a new task named "Total" will be createdand displayed at the end of the active project. The Number1 field in thistask will contain the sum of the Number1 values for all other visibletasks.

Microsoft Project 2010 and 2013

  1. On the View ribbon, select Macros and click View Macros.
  2. Enter the name "SumFilteredList and click Create.
  3. Copy the code from step 6 below into Module1 which is open in the Visual Basic editor.
To use this macro, do the following:

  1. Select a task view that uses a table, such as the Gantt Chart.
  2. Display only the tasks you want to calculate a total for, either by applying a filter or by hiding or showing subtasks. 
  3. On the View ribbon, select Macros, and then click View Macros.
  4. In the Macro list, select SumFilteredList, and then click Run.


Microsoft Project 98, 2000, 2002, 2003, and 2007
  1. On the Tools menu, point to Macro, and then click Macros.
  2. In the Macro Name field, type SumFilteredList. Click Create.
  3. Microsoft Project 2010
  4. Select the View tab, and click Macros
  5. In the Macros dialog box, type SumFilteredList in the Macro Name filed and click Create.
  6. In the Visual Basic Editor, enter the following code:
          Sub SumFilteredList()      ' This macro totals up the values in the Number1 field      ' for all visible (filtered) tasks, and displays this total      ' at the bottom of the task list.      Dim oTask As Task          ' Current task pointer      Dim oTotalTask As Task     ' Totals task pointer      Dim fTotal As Single       ' Used to hold totals         ' Ignore error generated by trying to delete a task         ' that does not exist.         On Error Resume Next         ' If totals task is present, delete it.         ActiveProject.Tasks("Total:").Delete         ' Re-enable default error handling         On Error GoTo 0         ' Select visible tasks         SelectAll         ' If tasks are selected, then loop through each of them         If Not (ActiveSelection.Tasks Is Nothing) Then            For Each oTask In ActiveSelection.Tasks                ' Add Number1 for each task to total               fTotal = fTotal + oTask.Number1   ' Field to Sum            Next oTask         Else            ' There's nothing to add            Exit Sub         End If          ' Create Totals task so it's visible         Set oTotalTask = ActiveProject.Tasks.Add("Total:")         ' Insure "Totals:" task is at outline level 1 (not indented)         Do Until oTotalTask.OutlineLevel = 1              oTotalTask.OutlineOutdent         Loop         ' Put total in proper field         oTotalTask.Number1 = fTotal             ' Field containing total         ' Hide task bar for this task         oTotalTask.HideBar = True         ' Select totals row         SelectRow oTotalTask.ID, False         ' Set font to bold         FontBold Set:=True      End Sub						

Microsoft Project 4.x

  1. On the Tools menu, click Macros.
  2. Click New.
  3. In the Macro Name field, type SumFilteredList. Click OK.
  4. In the Module Editor, enter the following code:
          Sub SumFilteredList()      ' This macro totals up the values in the Number1 field      ' for all visible (filtered) tasks, and displays this total      ' at the bottom of the task list.      Dim oTask As Task          ' Current task pointer      Dim oTotalTask As Task     ' Totals task pointer      Dim fTotal As Single       ' Used to hold totals         ' Ignore error generated by trying to delete a task         ' that does not exist.         On Error Resume Next         ' If totals task is present, delete it.         ActiveProject.Tasks("Total:").Delete         ' Re-enable default error handling         On Error GoTo 0         ' Select visible tasks         SelectAll         ' If tasks are selected, then loop through each of them         If Not (ActiveSelection.Tasks Is Nothing) Then            For Each oTask In ActiveSelection.Tasks                ' Add Number1 for each task to total               fTotal = fTotal + oTask.Number1   ' Field to Sum            Next oTask         Else            ' There's nothing to add            Exit Sub         End If          ' Create Totals task so it's visible         Set oTotalTask = ActiveProject.Tasks.Add("Total:")         ' Insure "Totals:" task is at outline level 1 (not indented)         Do Until oTotalTask.OutlineLevel = 1              oTotalTask.OutlineOutdent         Loop         ' Put total in proper field         oTotalTask.Number1 = fTotal             ' Field containing total         ' Hide task bar for this task         oTotalTask.HideBar = True         ' Select totals row         SelectRow oTotalTask.ID, False         ' Set font to bold         FontBold Set:=True      End Sub						
To use this macro, do the following:

  1. Select a task view that uses a table, such as the Gantt Chart.
  2. Display only the tasks you want to calculate a total for either by applying a filter or by hiding or showing subtasks.
  3. On the Tools menu, click Macros.
  4. In the Macro list, select SumFilteredList, and then click Run.
To use this macro, do the following:

  1. Select a task view that uses a table, such as the Gantt Chart.
  2. Display only the tasks you want to calculate a total for either by applying a filter or by hiding or showing subtasks.
  3. On the Tools menu, point to Macro, and then click Macros.
  4. In the Macro list, select SumFilteredList, and then click Run.

Modifying the Macro

To sum values from fields other than Number1, replace the reference toNumber1 on the line commented with "Field to sum" with the name of thefield for which you want to derive a total. To place the total in a fieldother than Number1, replace the reference to Number1 on the line commentedwith "Field containing total" with the name of the field into which youactually want to place the total.

For example, if the field you want to sum is a calculated field, such asCost or Actual Cost, then the sum must be placed in a non-calculated field,such as Cost1 or Number1. Therefore, to get the total cost fields for allvisible tasks and place the total in the Cost1 field of the "Total" task,you would change the indicated lines as follows:
   fTotal = fTotal + oTask.Cost   ' Field to Sum   oTotalTask.Cost1 = fTotal      ' Field containing total				
Properties

Article ID: 124482 - Last Review: 04/29/2013 18:25:00 - Revision: 6.0

Microsoft Project 98 Standard Edition, Microsoft Project 4.1 Standard Edition, Microsoft Project 4.0 Standard Edition, Microsoft Project 4.0 for Macintosh, Microsoft Office Project Standard 2007, Microsoft Office Project Standard 2003, Microsoft Project 2000 Standard Edition, Microsoft Project 2002 Standard Edition, Microsoft Project Standard 2010, Microsoft Office Project Professional 2007, Microsoft Office Project Professional 2003, Microsoft Office Project Server 2007, Microsoft Office Project Server 2003, Microsoft Project 2002 Professional Edition, Microsoft Project Professional 2010, Microsoft Project Server 2002, Microsoft Project Server 2010, Microsoft Project 2013 Professional, Microsoft Project 2013 Standard

  • kbcode kbhowto kbprogramming KB124482
Feedback
html>ript> https://c.microsoft.com/ms.js"> oft.com/c.gif?DI=4050&did=1&t=">ar varCustomerTracking = 1; var Route = "76500"; var Ctrl = ""; document.write(" var Route = "76500"; var Ctrl = ""; document.write("