How to Create Subtasks Under Summary Tasks using VBA in Microsoft Project
Question details
The user needs to automate the generation of three specific subtasks under existing summary tasks imported from Excel.
- Product
- Microsoft Project
- Device & OS
- not provided
- Scenario
- Organizing imported Excel data by automatically generating standardized subtask phases (Conception, Production, Realization) for each project summary task.
- Observed behavior
- Creating standard subtasks manually for every summary task is time-consuming and inefficient. The goal is to use a VBA macro that loops through existing summary tasks, references a specific task field for the project identifier, and automatically builds the corresponding indented subtasks.
Before running the macro, ensure you have backed up your Microsoft Project file and verified that all your summary tasks contain the correct project identifier in the 'Text1' field.
Use a VBA Macro to Automatically Generate Subtasks
Implement a custom VBA script to loop through existing summary tasks, read their project identifier, and insert three perfectly structured subtasks for each.
This macro iterates over all tasks currently in your project. To prevent an infinite loop (which could occur as the macro adds new subtasks into the task list), it calculates the initial count of tasks first. It reads the project identifier from the 'Text1' field to appropriately name each new subtask, then sets the outline level to 2 so they become immediate children of the summary task.
In Microsoft Project, press 'Alt + F11' on your keyboard to open the Visual Basic for Applications (VBA) editor.
Click on 'Insert' in the top menu and select 'Module' to create a new blank script area.
Copy and paste the following code into the module window: ```vba Sub MakeSubTsk() Dim t As Task Dim Area As Tasks Dim NumProj As Integer Dim i As Integer, j As Integer, k As Integer Dim SubNam As String Set Area = ActiveProject.Tasks NumProj = Area.Count j = 0 k = 1 For Each t In Area SubNam = t.Text1 & " Realisation" For i = 3 To 1 Step -1 If i = 2 Then SubNam = t.Text1 & " Production" If i = 1 Then SubNam = t.Text1 & " Conception" t.OutlineChildren.Add Name:=SubNam, Before:=t.ID + 1 SetTaskField Field:="Outline Level", Value:=2, TaskID:=t.ID + k k = k + 1 Next i k = 1 j = j + 1 If j = NumProj Then Exit For Next t End Sub ```
Ensure your summary tasks have valid identifiers in the 'Text1' column, then press 'F5' or click the 'Run' button in the toolbar to execute the script and generate your subtasks.
Manage Project Schedules with WPS Spreadsheet
If you don't require the complex VBA macros of Microsoft Project, WPS Office serves as a highly efficient, free alternative for project management. You can easily build Gantt charts, track task hierarchies, and collaborate efficiently using WPS Spreadsheet.
- 1. Download and Install WPS Office: Get WPS Office for free from the official website and easily install it on your device.
- 2. Open a Project Template: Launch WPS Spreadsheet, navigate to the Template Library, and search for 'Project Management' or 'Gantt Chart'.
- 3. Customize Your Tasks: Enter your summary tasks and subtasks directly into the spreadsheet, taking advantage of formulas and formatting to track progress.
Frequently Asked Questions
Can I run this macro on specific selected tasks instead of the entire project?
Yes, you can modify the VBA code to loop through `ActiveSelection.Tasks` instead of `ActiveProject.Tasks`. This ensures the subtasks are only added to the summary tasks you have actively highlighted in the project view.
What happens if the 'Text1' field is empty when running the macro?
If the 'Text1' field is empty, the macro will still create the three subtasks, but their names will only contain the suffixes (e.g., ' Conception' instead of 'Project1 Conception'). To prevent this, ensure every summary task has a project identifier filled in the 'Text1' column before executing.
How do I export my MS Project tasks to an Excel or WPS Spreadsheet format?
In Microsoft Project, navigate to File > Save As, and choose 'Excel Workbook (*.xlsx)' as your format. You can map the necessary fields during the export wizard. The resulting file can be opened directly in WPS Spreadsheet for seamless task tracking.
