Why does my pivot table not include all my fields in my VBA code (keeps excluding the 2nd xlDataField)?

[tyra]

HiI have created a pivot table with the below code. However it does not include the second data field "Minutes" for some reason. All the fields are in range, when I switch them around and have "Minutes" first it includes it but not "TRUST No" vice versa. I don't know where I'm going wrong please help. Thank you in advance!

Code;
>>> use code - tags <<<

Sub CreatePivotTable()
Dim wb As Workbook
Dim dsheet As Worksheet
Dim pc As PivotCache
Dim pt As PivotTable
Dim ptsheet As Worksheet
Dim lr As Long
Dim lc As Long
Dim prange As Range

Set wb = ActiveWorkbook
Set dsheet = ActiveSheet

`Check sheet name`
If Left(ActiveSheet.Name, 2) <> "EK" Then
MsgBox "This is not the active sheet, select EK daily tins ****** sheet and try again.", vbExclamation
Exit Sub
End If

`Find last row and column`
lr = dsheet.Cells(dsheet.Rows.Count, 1).End(xlUp).Row
lr = lr - 5 'Exclude first 4 and last rows of data'
lc = dsheet.Cells(5, dsheet.Columns.Count).End(xlToLeft).Column

`Set range to resize based on number of column and rows`
Set prange = dsheet.Cells(5, 1).Resize(lr, lc)

`Create new worksheet for pivot table`
Worksheets.Add after:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Set ptsheet = Worksheets("PivotTable")

`Create pivot cache and pivot table`
Set pc = wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=prange)
Set pt = pc.CreatePivotTable(TableDestination:=ptsheet.Range("A1"), TableName:="EK Tins")

`Add pivot fields`
With ptsheet.PivotTables("EK Tins").PivotFields("Resp Man")
.Orientation = xlRowField
End With

With ptsheet.PivotTables("EK Tins").PivotFields("TRUST No")
.Orientation = xlDataField
.PivotFields("TRUST No").Function = xlCount
.Position = 1
End With

With ptsheet.PivotTables("EK Tins").PivotFields("Minutes")
.Orientation = xlDataField
.PivotFields("Minutes").Function = xlSum
.Position = 2
End With

End Sub

My goal is for the pivot table field to look like:

 

[Debaser]

FYI, cross-posted here: https://forum.ozgrid.com/forum/inde...ields-are-being-included-into-the-pivot-table

 

[vletm]

tyra​

As written in Forum Rules and those are for You too:

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

 

[tyra]

This is the correct code for those that may be having the same issue.

>>> use code - tags <<<

Sub CreatePivotTable()

   Dim wb As Workbook
   Dim ws As Worksheet
   Dim pc As PivotCache
   Dim pt As PivotTable
   Dim ptsheet As Worksheet
   Dim lr As Long
   Dim lc As Long
   Dim prange As Range
  
   Set ws = ActiveSheet
  
   Set wb = ActiveWorkbook
  
   If Left(ActiveSheet.Name, 2) <> "EK" Then
  
      MsgBox "This is not the active sheet, select EK daily tins ****** sheet and try again.", vbExclamation
      Exit Sub
  
   End If
  
   'Find last row and column'
  
   lr = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
   lr = lr - 5 'Exclude first 4 and last rows of data'
  
   lc = ws.Cells(5, ws.Columns.Count).End(xlToLeft).Column
  
   'Set range to resize based on number od column and rows'
   Set prange = ws.Cells(5, 1).Resize(lr, lc)
  
   'Create new worksheet for pivot table'
   Set ptsheet = Worksheets.Add(after:=ActiveSheet)
  
   ptsheet.Name = "PivotTable"
     
   'Create pivot cache and pivot table'
   Set pc = wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=prange)
  
   Set pt = pc.CreatePivotTable(TableDestination:=ptsheet.Range("A1"), TableName:="EK Tins")
  
   'Add pivot fields'
  
   With pt
  
      .PivotFields("Resp Man").Orientation = xlRowField
  
      With .PivotFields("TRUST No")
     
         .Orientation = xlDataField
         .Function = xlCount
         .Position = 1
     
      End With
     
      With .PivotFields("Minutes")
     
         .Orientation = xlDataField
         .Function = xlSum
         .Position = 2
     
      End With
   End With
End Sub