Create Pivot Table Macro

Jones1413

New Member
Joined
Jul 26, 2019
Messages
25
Hello,

I have a worksheet of data that I want to create a Pivot Table out of. The number of rows and columns of data change weekly. I have the below macro but when I run the macro, nothing populates on the newly created worksheet. I'm not sure what I am missing and why the pivot table is not being created.

VBA Code:
Sub Pivot_Table()

'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
ActiveSheet.Name = "Report"
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "Team Req Pivot"
Application.DisplayAlerts = True
Set PSheet = Worksheets("Team Req Pivot")
Set DSheet = Worksheets("Report")

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

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

With ActiveSheet.PivotTables("TeamReq").PivotFields("Current Status")
.Orientation = xlPageField
.Position = 1
End With

With ActiveSheet.PivotTables("TeamReq").PivotFields("Recruiter Name")
.Orientation = xlRowField
.Position = 1
    End With

End Sub
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,719
Members
414,401
Latest member
grenona2020

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