Thank you for taking the time to help,
I am using VBA to create a pivot table that will be used to view staffing / allocations over a number of dates.
The data fields presently in the pivot table macro are separated in one column grouped in rows, Position 1 through to 4. "Actual work" is excluded
Date
workplace
shift time
and lastly info about the person on that shift ("Assignment Info")
My first problem i am having is i cannot exclude the persons in the pivot field "Shifts" (Position 3) that get grouped as being "OFF" work that day from the table.
The second problem is this:
I need to create a column in the pivot table "c" that gives a numerical count of the persons "assignment info" on shift that day at each workplace. i.e "1" in the column value
I also need an additional column that gives a numerical count of the persons "on call" that day for each workplace. Note that the "shift" field has a sub grouping of "O/C"
Please find below a mocksheet (This is the raw data i receive) and my macro thus far to create a pivot table,
I am using VBA to create a pivot table that will be used to view staffing / allocations over a number of dates.
The data fields presently in the pivot table macro are separated in one column grouped in rows, Position 1 through to 4. "Actual work" is excluded
Date
workplace
shift time
and lastly info about the person on that shift ("Assignment Info")
My first problem i am having is i cannot exclude the persons in the pivot field "Shifts" (Position 3) that get grouped as being "OFF" work that day from the table.The second problem is this:
I need to create a column in the pivot table "c" that gives a numerical count of the persons "assignment info" on shift that day at each workplace. i.e "1" in the column valueI also need an additional column that gives a numerical count of the persons "on call" that day for each workplace. Note that the "shift" field has a sub grouping of "O/C"Please find below a mocksheet (This is the raw data i receive) and my macro thus far to create a pivot table,
VBA Code:
Sub InsertPivotTable()
'Declare Variables
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long
'Insert a New Blank Worksheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("Allocation.AssignedDuties_Brows")
'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)
'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="Pivot")
'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="Pivot")
'Insert Row Fields
With ActiveSheet.PivotTables("Pivot").PivotFields("Duty Date")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("Pivot").PivotFields("Owning Unit")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("Pivot").PivotFields("Shift")
.Orientation = xlRowField
.Position = 3
'This is where i need to exclude the people as being rostered off that are grouped into "OFF"
End With
With ActiveSheet.PivotTables("Pivot").PivotFields("Assignment Info")
.Orientation = xlRowField
.Position = 4
End With
'Insert Data Field
'This is where i need to count the number of "on call" in a seperate column "d" in the pivot table
'This is where i need to count the number of "assignment info" in this sub grouping of different shift times
'in a seperate column "c" in the pivot table
'Format Pivot Table
ActiveSheet.PivotTables("Pivot").ShowTableStyleRowStripes = True
ActiveSheet.PivotTables("Pivot").TableStyle2 = "PivotStyleMedium9"
End Sub
