Hello All,

I have the following code which brings out the count on a pivot table, I read you can't just do xlSum so I found some other code online ( at the bottom)'Commented out, which I ran as a seperate macro which does change it to Sum but the results are just Zeros. Any Ideas?
Thanks

Ross

Sub Test()

 


'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 = "DB Absence Graph"
Application.DisplayAlerts = True
Set PSheet = Worksheets("DB Absence Graph")
Set DSheet = Worksheets("DB Absence")

'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:="DB Absence")

'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="DB Absence")



'Insert Column Fields
'With ActiveSheet.PivotTables("DB Absence").PivotFields("Total absence days")
'.Orientation = xlColumnField
'.Position = 1
'End With









ActiveSheet.PivotTables("DB Absence").AddDataField ActiveSheet.PivotTables( _
        "DB Absence").PivotFields("Level 3"), "Sum of Total absence days", xlCount
        

'Insert Row Fields
With ActiveSheet.PivotTables("DB Absence").PivotFields("Level 3")
.Orientation = xlRowField
.Position = 1





End With
'Format Pivot Table
ActiveSheet.PivotTables("DB Absence").ShowTableStyleRowStripes = True
ActiveSheet.PivotTables("DB Absence").TableStyle2 = "PivotStyleMedium9"


'Dim pf As PivotField
'With Selection.PivotTable
'.ManualUpdate = True
'For Each pf In .DataFields
'With pf
'.Function = xlSum
'.NumberFormat = "#,##0"
'End With
'Next pf
'.ManualUpdate = False
 
End With

Dim number1 As Long, Answer As Long, Percentage As Single
Dim Value1 As Single, v As Long, u As Long, wh As Worksheet, wa As Worksheet
Set wh = Sheets("DB Headcount"): Set wa = Sheets("DB Absence")
v = wh.Range("A2").End(xlDown).Row - 1
u = wa.Range("T2").End(xlDown).Row - 1

Value1 = Application.Sum(wa.Range("T:T"))
number1 = 22
Answer = v * number1
Percentage = Value1 / Answer

Range("a1").Value = Percentage
Range("A1").NumberFormat = "0.00%"




Sheets("DB Absence Graph").Select
    Sheets("DB Absence").Move Before:=Sheets(2)


End Sub