Pivot table will not generate using VBA

DarrenK

Board Regular
Joined
Aug 5, 2017
Messages
65
Office Version
  1. 365
Platform
  1. Windows
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:
ABCDEFGH
I​
J​
Agent Name*Total CallsUnscheduled Break*Lunch Time*Break TimeP-ACP-CallbackP-EmailP-ResearchWrap TOTAL
John5700:15:3200:30:3800:40:5800:01:4800:21:3200:07:0300:00:0001:26:32
George4400:16:4200:29:5300:32:2900:00:0000:30:0501:04:2500:00:0000:52:56
Paul3400:03:3300:29:4200:31:4700:00:0000:00:0001:25:0100:00:0001:29:10
Ringo3500:01:3700:31:4700:32:5200:00:0002:25:2100:00:0000:00:2600: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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
15,379
Office Version
  1. 2013
Platform
  1. Windows
Note: When you put code in the forum use code tag, to make it easier to read and copy

1650566391095.png


-----
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:
VBA Code:
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

Board Regular
Joined
Aug 5, 2017
Messages
65
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Aug 5, 2017
Messages
65
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
This is just a test to see if I did it right. Never knew about this before
 

Forum statistics

Threads
1,181,057
Messages
5,927,876
Members
436,574
Latest member
Bosniain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top