Generic VBA to create Pivot Table returns error

[toci1980]

Hello folks,

I am trying to create a VBA code which will create a Pivot Table and for this purpose I have been using generic code (recorded one) that I modified a little bit.

The problem starts when it comes to put/add filed into "Values" .

This is the code:

Sub create_pivot()

Dim mysourcedata, mydestination As String
Dim lr As Long

Sheets.Add
ActiveSheet.Name = "Pivot_Sheet"

lr = Sheets("Sheet1").Range("A1").End(xlDown).Row '' find your last row with data

mysourcedata = "Sheet1!R1C1:R" & lr & "C16"
mydestination = "Pivot_Sheet!R1C1"

    Sheets("Pivot_Sheet").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        mysourcedata, Version:=6).CreatePivotTable TableDestination:= _
        mydestination, TableName:="PivotTable1", DefaultVersion:=6
      
    
With ActiveSheet.PivotTables("PivotTable1")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Isporuka")
        .Orientation = xlRowField
        .Position = 1
        
    End With
    
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("Kolicina podele"), "Sum of Kolicina podele", xlSum
      
          
End Sub

I have tried several solutions for the code line:

 ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("Kolicina podele"), "Sum of Kolicina podele", xlSum

but nothing works.

Any idea what can be the problem and how to resolve it?

Thank you in advance.

 

[DanteAmor]

What does the error message say?

Check that in your columns you have a heading with a name identical to: "Kolicina podele"