stephenm93
New Member
- Joined
- May 6, 2016
- Messages
- 2
I'm a VBA novice but do have some code working that takes fields from a pivot table, pastes them to an additional worksheet, populates additional information, and creates an MS Project Gantt Chart. The issue I'm having originates from not being able to effectively apply multiple sorting to my source pivot table. Hence the values I'm populating to my additional worksheet are not always correctly sorted by date. As much as I've tried (lots of trial and error/failure) I cannot seem to properly code a means of reading through the rows, finding the identical task 'Name', and re-sorting only those tasks by 'Start' date. Notice the highlighted data have the same task name, but are sorted out of order by 'Start' date. Since this can happen more than once, I need the code to apply the sorting logic to each instance of these unsorted groups. Sorry for the big blob of data...I couldn't quite decipher how to paste the data as an image. Any and all help is greatly appreciated.
Here is the data copied from the pivot table to the Task_Table sheet and populated with data required by MS Project:
[TABLE="width: 1027"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Active[/TD]
[TD]Schedule[/TD]
[TD]Resource Name[/TD]
[TD]Start[/TD]
[TD]Name[/TD]
[TD]Duration[/TD]
[TD]Percent Complete[/TD]
[TD]Predescessor[/TD]
[TD]Outline Level[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Red Team[/TD]
[TD="align: right"]7/15/2017[/TD]
[TD]Unit #470H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Red Team[/TD]
[TD="align: right"]8/24/2017[/TD]
[TD]Unit #488H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Red Team[/TD]
[TD="align: right"]10/3/2017[/TD]
[TD]Unit #483H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Red Team[/TD]
[TD="align: right"]11/12/2017[/TD]
[TD]Unit #474H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Red Team[/TD]
[TD="align: right"]12/22/2017[/TD]
[TD]Unit #482H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Brown Team[/TD]
[TD="align: right"]9/1/2016[/TD]
[TD]Unit #614H[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Purple Team[/TD]
[TD="align: right"]4/1/2016[/TD]
[TD]Unit #614H[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Orange Team[/TD]
[TD="align: right"]11/5/2016[/TD]
[TD]Unit #614H[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Blue Team[/TD]
[TD="align: right"]10/16/2016[/TD]
[TD]Unit #076H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Blue Team[/TD]
[TD="align: right"]11/25/2016[/TD]
[TD]Unit #075H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Blue Team[/TD]
[TD="align: right"]1/4/2017[/TD]
[TD]Unit #074H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Blue Team[/TD]
[TD="align: right"]2/13/2017[/TD]
[TD]Unit #072H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Blue Team[/TD]
[TD="align: right"]3/25/2017[/TD]
[TD]Unit #027H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Blue Team[/TD]
[TD="align: right"]5/4/2017[/TD]
[TD]Unit #062H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Blue Team[/TD]
[TD="align: right"]6/13/2017[/TD]
[TD]Unit #011H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Blue Team[/TD]
[TD="align: right"]7/23/2017[/TD]
[TD]Unit #070H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Green Team[/TD]
[TD="align: right"]7/7/2016[/TD]
[TD]Unit #058H[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Green Team[/TD]
[TD="align: right"]8/12/2016[/TD]
[TD]Unit #073H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Green Team[/TD]
[TD="align: right"]9/21/2016[/TD]
[TD]Unit #060H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]27[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Green Team[/TD]
[TD="align: right"]10/31/2016[/TD]
[TD]Unit #029H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]28[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Green Team[/TD]
[TD="align: right"]12/10/2016[/TD]
[TD]Unit #028H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]29[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Green Team[/TD]
[TD="align: right"]1/19/2017[/TD]
[TD]Unit #068H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Green Team[/TD]
[TD="align: right"]2/28/2017[/TD]
[TD]Unit #030H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]31[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Green Team[/TD]
[TD="align: right"]4/9/2017[/TD]
[TD]Unit #031H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]32[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Green Team[/TD]
[TD="align: right"]5/19/2017[/TD]
[TD]Unit #069H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]33[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Unassigned 1[/TD]
[TD="align: right"]8/31/2016[/TD]
[TD]Unit #486H[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]34[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Unassigned 2[/TD]
[TD="align: right"]6/17/2016[/TD]
[TD]Unit #486H[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]35[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Unassigned 3[/TD]
[TD="align: right"]12/1/2016[/TD]
[TD]Unit #486H[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]36[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Yellow Team[/TD]
[TD="align: right"]8/10/2016[/TD]
[TD]Unit #146H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]37[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Yellow Team[/TD]
[TD="align: right"]9/19/2016[/TD]
[TD]Unit #314H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]38[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Yellow Team[/TD]
[TD="align: right"]10/29/2016[/TD]
[TD]Unit #336H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]39[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Yellow Team[/TD]
[TD="align: right"]12/8/2016[/TD]
[TD]Unit #024H[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]40[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Yellow Team[/TD]
[TD="align: right"]2/7/2017[/TD]
[TD]Unit #322H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]41[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Yellow Team[/TD]
[TD="align: right"]3/19/2017[/TD]
[TD]Unit #335H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]42[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Yellow Team[/TD]
[TD="align: right"]4/28/2017[/TD]
[TD]Unit #321H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]43[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Yellow Team[/TD]
[TD="align: right"]6/7/2017[/TD]
[TD]Unit #1[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]44[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Yellow Team[/TD]
[TD="align: right"]7/17/2017[/TD]
[TD]Unit #207H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]45[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Yellow Team[/TD]
[TD="align: right"]8/26/2017[/TD]
[TD]Unit #208H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]46[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Yellow Team[/TD]
[TD="align: right"]10/5/2017[/TD]
[TD]Unit #209H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]47[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Yellow Team[/TD]
[TD="align: right"]11/14/2017[/TD]
[TD]Unit #210H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
Here is my current code to populate this sheet:
<code><code>
Sub Task_AssignTables()
Dim task As Worksheet
Dim assignment As Worksheet
Dim pt As PivotTable
Dim idStart As Range
Dim resStart As Range 'resource table name range
Dim NameStart As Range 'task table name range
Dim NameColumn As Range 'task table range containing well names
Dim NameRowArr() As Long 'task table cells in NameColumn range
Dim predStart As Range 'task table predescessor range
Dim pctRange As Range 'task table percent complete range
Dim FindColumn As Range 'range to find the resource column if it has moved
Dim pvtName As Range
Dim pvtRigName As Range 'pivot table Rig Name field
Dim pvtJobStart As Range 'pivot table Job Start field
Dim pvtJobDays As Range 'pivot table Job Duration
Dim pvtPctComp As Range 'pivot table Percent Complete
Dim ProjectGroup As Range 'union of the pivot table fields
Dim assignTask As Range 'assignment table task name
Dim assignRes As Range 'assignment table resource name
Dim assignComp As Range 'assignment table work complete
Dim assignWork As Range 'assignment table work hours
Dim assignUnits As Range 'assignment table units
Dim RigColumn As Long
Dim LR As Long
Dim NameLR As Range
Dim i As Integer
Dim j As Integer
Dim p As Integer
'** PART 1 - copy the pivot table fields to the Task_Table and set the other MS Project field values
'copies the pivot table cache
Set pt = Sheet1.PivotTables(1)
Set task = ThisWorkbook.Sheets("Task_Table")
Set assignment = ThisWorkbook.Sheets("Assignment_Table")
LR = task.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row 'counts the rows in the Task Table
If LR <> 1 Then 'if more than header row exists, deletes any existing rows before refreshing the pivot cache
****task.Rows("2:" & LR).Delete
****assignment.Rows("2:" & LR).Delete
****Else
End If
'get the individual pivot table field values
****Set pvtName = pt.PivotFields("Well Name").DataRange
****Set pvtRigName = pt.PivotFields("Rig Name").DataRange
****Set pvtJobStart = pt.PivotFields("Job Start").DataRange
****Set pvtJobDays = pt.PivotFields("Job Days").DataRange
****Set pvtPctComp = pt.PivotFields("Percent Complete").DataRange
****Set ProjectGroup = Union(pvtJobStart, pvtName, pvtRigName, pvtJobDays, pvtPctComp) 'combine the fields into one contiguous range
****ProjectGroup.Copy
****
task.Range("d2").PasteSpecial (xlPasteValuesAndNumberFormats) 'pastes the group
task.Range("a2").CurrentRegion.Name = "PivotCache" 'names the range that was pasted for easy reference
LR = task.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row 'counts the pasted rows in the Task Table
'Search for the cell address with the Rig Name header
Set FindColumn = task.Range("PivotCache").Find("Resource Name", , xlValues, xlWhole)
****If Not FindColumn Is Nothing Then**' if FindColumn is something (Prevents Errors)
********RigColumn = FindColumn.Column******'set RigColumn as the address to the first time a match is found
****End If
****
'finds the last row of the Rig Name column within the pivot cache and fills in the blank rig names
With task.Range(task.Cells(1, RigColumn), task.Cells(LR, RigColumn))
****.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C" 'sets the formula to fill the rig names
****.Value = .Value 'overwrites the formula with the values
End With
'Fill Remaining Task and Assignment Table Fields
Set idStart = task.Cells(1, 1)
Set NameStart = task.Cells(1, 6)
Set resStart = task.Cells(1, RigColumn)
Set pctRange = task.Cells(1, 8)
Set predStart = task.Cells(1, 9)
Set assignTask = assignment.Cells(1, 1)
Set assignRes = assignment.Cells(1, 2)
Set assignComp = assignment.Cells(1, 3)
Set assignWork = assignment.Cells(1, 4)
Set assignUnits = assignment.Cells(1, 5)
****For i = 1 To LR - 1
********idStart.Offset(i).Value = i 'fills the ID column with sequential numbers
********If resStart.Offset(i) = resStart.Offset(i - 1) _
************Or NameStart.Offset(i) = NameStart.Offset(i - 1) Then
****************predStart.Offset(i).Value = i - 1 'sets the predecessor value to the previous ID
********Else
****************predStart.Offset(i).Value = "" 'if there is no predecessor, set to blank
********End If
********If pctRange.Offset(i).Value = "(blank)" Or pctRange.Offset(i).Value = "" Then
************pctRange.Offset(i).Value = 0
********End If
********assignTask.Offset(i).Value = task.Cells(i + 1, 6).Value 'fills the assignment table task name from task table
********assignRes.Offset(i).Value = task.Cells(i + 1, 4).Value 'fills the assignment table resource from task table resource
********assignWork.Offset(i).Value = CStr((task.Cells(i + 1, 7).Value * 8) & "h") 'fills the assignment table hours from the task table duration
****Next
task.Range(task.Cells(2, 2), task.Cells(LR, 2)).Value = "Yes" 'fills the task table active field
task.Range(task.Cells(2, 3), task.Cells(LR, 3)).Value = "Auto Scheduled" 'fills the task table schedule field
task.Range(task.Cells(2, 10), task.Cells(LR, 10)).Value = CStr("1") 'fills the task table outline level field
assignment.Range(assignment.Cells(2, 3), assignment.Cells(LR, 3)).Value = 0 'fills the assignment table work complete field
assignment.Range(assignment.Cells(2, 5), assignment.Cells(LR, 5)).Value = CStr("100%") 'fills the assignment table units field
Call resourceTable 'populate the resource table
End Sub
</code></code>
Here is the data copied from the pivot table to the Task_Table sheet and populated with data required by MS Project:
[TABLE="width: 1027"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Active[/TD]
[TD]Schedule[/TD]
[TD]Resource Name[/TD]
[TD]Start[/TD]
[TD]Name[/TD]
[TD]Duration[/TD]
[TD]Percent Complete[/TD]
[TD]Predescessor[/TD]
[TD]Outline Level[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Red Team[/TD]
[TD="align: right"]7/15/2017[/TD]
[TD]Unit #470H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Red Team[/TD]
[TD="align: right"]8/24/2017[/TD]
[TD]Unit #488H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Red Team[/TD]
[TD="align: right"]10/3/2017[/TD]
[TD]Unit #483H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Red Team[/TD]
[TD="align: right"]11/12/2017[/TD]
[TD]Unit #474H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Red Team[/TD]
[TD="align: right"]12/22/2017[/TD]
[TD]Unit #482H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Brown Team[/TD]
[TD="align: right"]9/1/2016[/TD]
[TD]Unit #614H[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Purple Team[/TD]
[TD="align: right"]4/1/2016[/TD]
[TD]Unit #614H[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Orange Team[/TD]
[TD="align: right"]11/5/2016[/TD]
[TD]Unit #614H[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Blue Team[/TD]
[TD="align: right"]10/16/2016[/TD]
[TD]Unit #076H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Blue Team[/TD]
[TD="align: right"]11/25/2016[/TD]
[TD]Unit #075H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Blue Team[/TD]
[TD="align: right"]1/4/2017[/TD]
[TD]Unit #074H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Blue Team[/TD]
[TD="align: right"]2/13/2017[/TD]
[TD]Unit #072H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Blue Team[/TD]
[TD="align: right"]3/25/2017[/TD]
[TD]Unit #027H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Blue Team[/TD]
[TD="align: right"]5/4/2017[/TD]
[TD]Unit #062H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Blue Team[/TD]
[TD="align: right"]6/13/2017[/TD]
[TD]Unit #011H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Blue Team[/TD]
[TD="align: right"]7/23/2017[/TD]
[TD]Unit #070H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Green Team[/TD]
[TD="align: right"]7/7/2016[/TD]
[TD]Unit #058H[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Green Team[/TD]
[TD="align: right"]8/12/2016[/TD]
[TD]Unit #073H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Green Team[/TD]
[TD="align: right"]9/21/2016[/TD]
[TD]Unit #060H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]27[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Green Team[/TD]
[TD="align: right"]10/31/2016[/TD]
[TD]Unit #029H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]28[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Green Team[/TD]
[TD="align: right"]12/10/2016[/TD]
[TD]Unit #028H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]29[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Green Team[/TD]
[TD="align: right"]1/19/2017[/TD]
[TD]Unit #068H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Green Team[/TD]
[TD="align: right"]2/28/2017[/TD]
[TD]Unit #030H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]31[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Green Team[/TD]
[TD="align: right"]4/9/2017[/TD]
[TD]Unit #031H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]32[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Green Team[/TD]
[TD="align: right"]5/19/2017[/TD]
[TD]Unit #069H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]33[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Unassigned 1[/TD]
[TD="align: right"]8/31/2016[/TD]
[TD]Unit #486H[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]34[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Unassigned 2[/TD]
[TD="align: right"]6/17/2016[/TD]
[TD]Unit #486H[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]35[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Unassigned 3[/TD]
[TD="align: right"]12/1/2016[/TD]
[TD]Unit #486H[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]36[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Yellow Team[/TD]
[TD="align: right"]8/10/2016[/TD]
[TD]Unit #146H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]37[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Yellow Team[/TD]
[TD="align: right"]9/19/2016[/TD]
[TD]Unit #314H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]38[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Yellow Team[/TD]
[TD="align: right"]10/29/2016[/TD]
[TD]Unit #336H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]39[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Yellow Team[/TD]
[TD="align: right"]12/8/2016[/TD]
[TD]Unit #024H[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]40[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Yellow Team[/TD]
[TD="align: right"]2/7/2017[/TD]
[TD]Unit #322H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]41[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Yellow Team[/TD]
[TD="align: right"]3/19/2017[/TD]
[TD]Unit #335H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]42[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Yellow Team[/TD]
[TD="align: right"]4/28/2017[/TD]
[TD]Unit #321H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]43[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Yellow Team[/TD]
[TD="align: right"]6/7/2017[/TD]
[TD]Unit #1[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]44[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Yellow Team[/TD]
[TD="align: right"]7/17/2017[/TD]
[TD]Unit #207H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]45[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Yellow Team[/TD]
[TD="align: right"]8/26/2017[/TD]
[TD]Unit #208H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]46[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Yellow Team[/TD]
[TD="align: right"]10/5/2017[/TD]
[TD]Unit #209H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]47[/TD]
[TD]Yes[/TD]
[TD]Auto Scheduled[/TD]
[TD]Yellow Team[/TD]
[TD="align: right"]11/14/2017[/TD]
[TD]Unit #210H[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
Here is my current code to populate this sheet:
<code><code>
Sub Task_AssignTables()
Dim task As Worksheet
Dim assignment As Worksheet
Dim pt As PivotTable
Dim idStart As Range
Dim resStart As Range 'resource table name range
Dim NameStart As Range 'task table name range
Dim NameColumn As Range 'task table range containing well names
Dim NameRowArr() As Long 'task table cells in NameColumn range
Dim predStart As Range 'task table predescessor range
Dim pctRange As Range 'task table percent complete range
Dim FindColumn As Range 'range to find the resource column if it has moved
Dim pvtName As Range
Dim pvtRigName As Range 'pivot table Rig Name field
Dim pvtJobStart As Range 'pivot table Job Start field
Dim pvtJobDays As Range 'pivot table Job Duration
Dim pvtPctComp As Range 'pivot table Percent Complete
Dim ProjectGroup As Range 'union of the pivot table fields
Dim assignTask As Range 'assignment table task name
Dim assignRes As Range 'assignment table resource name
Dim assignComp As Range 'assignment table work complete
Dim assignWork As Range 'assignment table work hours
Dim assignUnits As Range 'assignment table units
Dim RigColumn As Long
Dim LR As Long
Dim NameLR As Range
Dim i As Integer
Dim j As Integer
Dim p As Integer
'** PART 1 - copy the pivot table fields to the Task_Table and set the other MS Project field values
'copies the pivot table cache
Set pt = Sheet1.PivotTables(1)
Set task = ThisWorkbook.Sheets("Task_Table")
Set assignment = ThisWorkbook.Sheets("Assignment_Table")
LR = task.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row 'counts the rows in the Task Table
If LR <> 1 Then 'if more than header row exists, deletes any existing rows before refreshing the pivot cache
****task.Rows("2:" & LR).Delete
****assignment.Rows("2:" & LR).Delete
****Else
End If
'get the individual pivot table field values
****Set pvtName = pt.PivotFields("Well Name").DataRange
****Set pvtRigName = pt.PivotFields("Rig Name").DataRange
****Set pvtJobStart = pt.PivotFields("Job Start").DataRange
****Set pvtJobDays = pt.PivotFields("Job Days").DataRange
****Set pvtPctComp = pt.PivotFields("Percent Complete").DataRange
****Set ProjectGroup = Union(pvtJobStart, pvtName, pvtRigName, pvtJobDays, pvtPctComp) 'combine the fields into one contiguous range
****ProjectGroup.Copy
****
task.Range("d2").PasteSpecial (xlPasteValuesAndNumberFormats) 'pastes the group
task.Range("a2").CurrentRegion.Name = "PivotCache" 'names the range that was pasted for easy reference
LR = task.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row 'counts the pasted rows in the Task Table
'Search for the cell address with the Rig Name header
Set FindColumn = task.Range("PivotCache").Find("Resource Name", , xlValues, xlWhole)
****If Not FindColumn Is Nothing Then**' if FindColumn is something (Prevents Errors)
********RigColumn = FindColumn.Column******'set RigColumn as the address to the first time a match is found
****End If
****
'finds the last row of the Rig Name column within the pivot cache and fills in the blank rig names
With task.Range(task.Cells(1, RigColumn), task.Cells(LR, RigColumn))
****.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C" 'sets the formula to fill the rig names
****.Value = .Value 'overwrites the formula with the values
End With
'Fill Remaining Task and Assignment Table Fields
Set idStart = task.Cells(1, 1)
Set NameStart = task.Cells(1, 6)
Set resStart = task.Cells(1, RigColumn)
Set pctRange = task.Cells(1, 8)
Set predStart = task.Cells(1, 9)
Set assignTask = assignment.Cells(1, 1)
Set assignRes = assignment.Cells(1, 2)
Set assignComp = assignment.Cells(1, 3)
Set assignWork = assignment.Cells(1, 4)
Set assignUnits = assignment.Cells(1, 5)
****For i = 1 To LR - 1
********idStart.Offset(i).Value = i 'fills the ID column with sequential numbers
********If resStart.Offset(i) = resStart.Offset(i - 1) _
************Or NameStart.Offset(i) = NameStart.Offset(i - 1) Then
****************predStart.Offset(i).Value = i - 1 'sets the predecessor value to the previous ID
********Else
****************predStart.Offset(i).Value = "" 'if there is no predecessor, set to blank
********End If
********If pctRange.Offset(i).Value = "(blank)" Or pctRange.Offset(i).Value = "" Then
************pctRange.Offset(i).Value = 0
********End If
********assignTask.Offset(i).Value = task.Cells(i + 1, 6).Value 'fills the assignment table task name from task table
********assignRes.Offset(i).Value = task.Cells(i + 1, 4).Value 'fills the assignment table resource from task table resource
********assignWork.Offset(i).Value = CStr((task.Cells(i + 1, 7).Value * 8) & "h") 'fills the assignment table hours from the task table duration
****Next
task.Range(task.Cells(2, 2), task.Cells(LR, 2)).Value = "Yes" 'fills the task table active field
task.Range(task.Cells(2, 3), task.Cells(LR, 3)).Value = "Auto Scheduled" 'fills the task table schedule field
task.Range(task.Cells(2, 10), task.Cells(LR, 10)).Value = CStr("1") 'fills the task table outline level field
assignment.Range(assignment.Cells(2, 3), assignment.Cells(LR, 3)).Value = 0 'fills the assignment table work complete field
assignment.Range(assignment.Cells(2, 5), assignment.Cells(LR, 5)).Value = CStr("100%") 'fills the assignment table units field
Call resourceTable 'populate the resource table
End Sub
</code></code>