BartD
09-15-2017, 12:47 AM
Hi excel gurus,
I am currently trying to make a pivot pivot tables with dynamic range, but it is not really working.
I have used the code below for one pivot table, wich worked perfectly, but when i use it again for another pivot table it does create a new sheet, but it does not create the pivot table.
Please help me out and thanks in advance!
Gr,
Bart
Sub Urgency()
'Create pivot
'Declare Variables
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Entity").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "Entity"
Application.DisplayAlerts = True
Set PSheet = Worksheets("Entity")
Set DSheet = Worksheets("Demand")
'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:="EntityPivotTable")
'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="EntityPivotTable")
End Sub
I am currently trying to make a pivot pivot tables with dynamic range, but it is not really working.
I have used the code below for one pivot table, wich worked perfectly, but when i use it again for another pivot table it does create a new sheet, but it does not create the pivot table.
Please help me out and thanks in advance!
Gr,
Bart
Sub Urgency()
'Create pivot
'Declare Variables
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Entity").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "Entity"
Application.DisplayAlerts = True
Set PSheet = Worksheets("Entity")
Set DSheet = Worksheets("Demand")
'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:="EntityPivotTable")
'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="EntityPivotTable")
End Sub