Pivot table will not generate using VBA

[DarrenK]

This is going to be a basic question. I've looked online and have formulated what I believe to be correct. The worksheet will generate but it doesn't display any data. Is it the headers? What is wrong? I just want a pivot table that compares Columns F:I.

My source data is formulated as follows:

A	B	C	D	E	F	G	H	I​	J​
Agent Name	*Total Calls	Unscheduled Break	*Lunch Time	*Break Time	P-AC	P-Callback	P-Email	P-Research	Wrap TOTAL
John	57	00:15:32	00:30:38	00:40:58	00:01:48	00:21:32	00:07:03	00:00:00	01:26:32
George	44	00:16:42	00:29:53	00:32:29	00:00:00	00:30:05	01:04:25	00:00:00	00:52:56
Paul	34	00:03:33	00:29:42	00:31:47	00:00:00	00:00:00	01:25:01	00:00:00	01:29:10
Ringo	35	00:01:37	00:31:47	00:32:52	00:00:00	02:25:21	00:00:00	00:00:26	00:53:28

Sub PIVOT()
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


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("Sheet1")

LastRow = DSheet.Cells(Rows.Count, 1).End(x1Up).Row
LastCol = DSheet.Cells(1, Columns.Count).End(x1ToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=x1Database, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="Pivot")

Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="Pivot")

With ActiveSheet.PivotTables("Pivot").PivotFields("P-AC")
.Orientation = x1RowField
.Position = 1
End With

With ActiveSheet.PivotTables("Pivot").PivotFields("P-Callback")
.Orientation = x1RowField
.Position = 2
End With

With ActiveSheet.PivotTables("Pivot").PivotFields("p-Email")
.Orientation = x1RowField
.Position = 3
End With

With ActiveSheet.PivotTables("Pivot").PivotFields("P-Research")
.Orientation = x1RowField
.Position = 4
End With

ActiveSheet.PivotTables("Pivot").ShowTableStyleRowStripes = True
ActiveSheet.PivotTables("Pivot").TableStyle2 = "PivotStyleMedium9"

End Sub

 

[DanteAmor]

Note: When you put code in the forum use code tag, to make it easier to read and copy

-----
1. It is not recommended to use the On Error statement, because there may be errors and you will not know what the problem is, simply with that statement the macro ignore the errors and continue the execution, but do nothing.

2. You put the number 1 and it should be the letter L

LastRow = DSheet.Cells(Rows.Count, 1).End(xLUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xLToLeft).Column
SourceType:=xLDatabase, SourceData:=PRange
.Orientation = xLRowField

I made some adjustments to the code, try the following:

Sub CREATE_PIVOT()
  Dim PSheet As Worksheet, DSheet As Worksheet
  Dim PTable As PivotTable
  Dim PRange As Range
  Dim LastRow As Long, LastCol As Long
  Dim sName As String
  
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False
  
  sName = "PivotTable"
  If Evaluate("ISREF('" & sName & "'!A1)") Then Worksheets(sName).Delete
  Sheets.Add(ActiveSheet).Name = sName
  Set PSheet = Worksheets(sName)
  Set DSheet = Worksheets("Sheet1")
  
  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)
  
  Set PTable = ActiveWorkbook.PivotCaches.Create(xlDatabase, PRange). _
    CreatePivotTable(PSheet.Range("A2"), "Pivot")
  With PTable
    .PivotFields("P-AC").Orientation = xlRowField
    .PivotFields("P-Callback").Orientation = xlRowField
    .PivotFields("p-Email").Orientation = xlRowField
    .PivotFields("P-Research").Orientation = xlRowField
    .ShowTableStyleRowStripes = True
    .TableStyle2 = "PivotStyleMedium9"
  End With

  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
End Sub

 

[DarrenK]

Sorry was out ill. They suggest always typing in VBA to help learn the coding better. I read (xlToLeft) as "times 1 to the left" which is why '1' made sense to me and the font didn't help matters. Live and learn. But the macro works as expected. Thank you for that. A couple questions though:

I may have formatted the PivotFields incorrectly. The 4 column headers that I am pulling data for are as follows:
Project- Account Correction
Project- Client Callback
Project- Client Email
Project- Research/Internal Submission

They're clunky but I didn't think them up. When I changed the PivotFields to the names above within the provided code, it didn't pull the correct sum total (even after I changed 'count' to 'sum')..

I would actually like the 4 items to go to the Values area in the Pivot table that comes up on the right side. With just the 'Σ Values' to appear in the Row area. How should the coding be updated to reflect that?

 

[DarrenK]

This is just a test to see if I did it right. Never knew about this before