Hello, i have trouble generating a pivot table with a range of data, at first i thought that i had to convert the range of data into a table and then generate the pivot table will solve the problem but it didn't and when leaving the range of data without the table format, the table is empty too.
This is my code:
This is my code:
VBA Code:
Sub Create()
Dim PCache As PivotCache
Dim TDinamica As PivotTable
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Dim tbl As ListObject
'Dim rng As Range
'Set rng = Range(Range("A1"), Range("A1").SpecialCells(xlLastCell))
'Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes)
'tbl.TableStyle = "TableStyleMedium15"
' With Range("A1")
'.Parent.ListObjects.Add(xlSrcRange, Range(.End(xlDown), .End(xlToRight)), , xlYes).Name = "Table1"
'End With
On Error Resume Next
Worksheets("TablaDinamica").Delete
'Crear Hoja TablaDinamica
Worksheets.Add(Before:=ActiveSheet).Name = "TablaDinamica"
Set PCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, SourceData:="Tabla1")
Set TDinamica = PCache.CreatePivotTable( _
TableDestination:="TablaDinamica!R3C1", TableName:="Tabla dinámica1")
With TDinamica.PivotFields("Request ID")
.Orientation = xlRowField
.Position = 1
End With
With TDinamica.PivotFields("Actual Cost")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
.NumberFormat = "#,##0"
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub