VBA : Creating Pivot Table

Question

My code has been showing expected expression error

The error that i am getting is from

ActiveWorkbook.Worksheets("Sheet2").PivotTables("PivotTable16").PivotCache. _
        CreatePivotTable TableDestination:="Sheet2!E3F16", TableName:="PivotTable17", DefaultVersion:=xlPivotTableVersionCurrent

Full macro:

Sub Macro5()
'
' Macro5 Macro
'

'
    ActiveCell.Select
    ActiveWorkbook.Worksheets("Sheet2").PivotTables("PivotTable16").PivotCache. _
        CreatePivotTable TableDestination:="Sheet2!E3F16", TableName:="PivotTable17", DefaultVersion:=xlPivotTableVersionCurrent

    Sheets("Sheet2").Select
    Cells(3, 16).Select
    With ActiveSheet.PivotTables("PivotTable17")
        .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
End Sub

`TableDestination:="Sheet2!E3F16"` - range address is not correct — Tim Williams, Aug 01 '19 at 23:34
Also you should Avoid Using Select. [Read](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Mikku, Aug 02 '19 at 00:18

score 0 · Answer 1 · answered Aug 02 '19 at 00:21

Code without Select and Correct Sheet Reference:

Sub Macro5()

    ActiveWorkbook.Worksheets("Sheet2").PivotTables("PivotTable16").PivotCache. _
        CreatePivotTable TableDestination:="Sheet2!E3:F16", TableName:="PivotTable17", DefaultVersion:=xlPivotTableVersionCurrent

    With Sheets("Sheet2").PivotTables("PivotTable17")
        .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
End Sub

Always Avoid Using Select in VBA, it causes only Problems.

Also Make sure Old PivotTable17 have been deleted before Running the Code, Otherwise you will face more issues.

VBA : Creating Pivot Table

1 Answers1