Heyy guys, I am struggling with problem. I have this code to create Pivot Table from data range:
I have tried several things, using variables and several types of code and always I struggle wit this bug, if anyone now how to tackle this, I will be grateful.
VBA Code:
Sub test4()
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
Set PSheet = Worksheets("Test 4")
Set DSheet = Worksheets("TB")
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)
On Error Resume Next
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
PRange, Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:=PSheet.Cells(14, 2), TableName:="Test 4", _
DefaultVersion:=xlPivotTableVersion15
Sheets("Test 4").Activate
With ActiveSheet.PivotTables("Test 4").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("Test 4").RepeatAllLabels xlRepeatLabels
With ActiveSheet.PivotTables("Test 4").PivotFields("AA")
.Orientation = xlRowField
.Position = 1
End With
Sheets("Test 4").Activate
Sheets("Test 4").Select
[COLOR=rgb(97, 189, 109)]'This Data field does not show up[/COLOR]
ActiveSheet.PivotTables("Test 4").AddDataField ActiveSheet. _
PivotTables("Test 4").PivotFields("Turnover"), _
"Sum of Turnover", xlSum
End Sub
I have tried several things, using variables and several types of code and always I struggle wit this bug, if anyone now how to tackle this, I will be grateful.
Last edited by a moderator: