Macro to Create Pivot Table on Existing WorkSheet

[qzyvxk]

Could someone please help me out i created a macro using the auto macro where i define the function (since i am a newbie to VBA)
i have a data dump on sheet 2(called dump) and sheet 1 is where i want the pivot data to reflect so i created a macro button and assigned the function as in recorded my keyboard clicks. i keep getting this error "Run Time error 1004. Application-defined or object-defined error.

This is the vba code reflected when i hit debug. Kindly help.

Sub CreatePIvot()
'
' CreatePIvot Macro
'


'
    Range("B7").Select
    Workbooks("Daily Dump.xlsm").Connections.Add2 _
        "WorksheetConnection_Dump!$D$1:$L$1500", "", _
        "WORKSHEET;C:\Users\Abhishek Mukherjee\Desktop\[Daily Dump.xlsm]Dump", _
        "Dump!$D$1:$L$1500", 7, True, False
[COLOR=#ffd700]    ActiveWorkbook.PivotCaches.create(SourceType:=xlExternal, SourceData:= _[/COLOR]
[COLOR=#ffd700]        ActiveWorkbook.Connections("WorksheetConnection_Dump!$D$1:$L$2000"), Version _[/COLOR]
[COLOR=#ffd700]        :=6).CreatePivotTable TableDestination:="Daily!R7C2", TableName:= _[/COLOR]
[COLOR=#ffd700]        "PivotTable1", DefaultVersion:=6[/COLOR]
    Cells(7, 2).Select
    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
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = True
        .CompactRowIndent = 1
        .VisualTotals = False
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = True
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .DisplayEmptyRow = False
        .DisplayEmptyColumn = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .DisplayImmediateItems = True
        .ViewCalculatedMembers = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = True
        .RowAxisLayout xlCompactRow
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotCache.RefreshOnFileOpen = False
    ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
    With ActiveSheet.PivotTables("PivotTable1").CubeFields("[Range].[Rating]")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").CubeFields.GetMeasure "[Range].[Rating]" _
        , xlCount, "Count of Rating"
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").CubeFields("[Measures].[Count of Rating]"), "Count of Rating"
    ActiveSheet.PivotTables("PivotTable1").CompactLayoutRowHeader = "Rating"
    Range("C7").Select
    ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
        "[Measures].[Count of Rating]").Caption = "[Measures].[Count of Rating]"
    Range("C7").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("E7").Select
    Workbooks("Daily Dump.xlsm").Connections.Add2 _
        "WorksheetConnection_Dump!$D$1:$L$2000", "", _
        "WORKSHEET;C:\Users\Abhishek Mukherjee\Desktop\[Daily Dump.xlsm]Dump", _
        "Dump!$D$1:$L$2000", 7, True, False
    ActiveWorkbook.PivotCaches.create(SourceType:=xlExternal, SourceData:= _
        ActiveWorkbook.Connections("WorksheetConnection_Dump!$D$1:$L$2000"), Version _
        :=6).CreatePivotTable TableDestination:="Daily!R7C5", TableName:= _
        "PivotTable2", DefaultVersion:=6
    Cells(7, 5).Select
    With ActiveSheet.PivotTables("PivotTable2")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = True
        .CompactRowIndent = 1
        .VisualTotals = False
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = True
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .DisplayEmptyRow = False
        .DisplayEmptyColumn = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .DisplayImmediateItems = True
        .ViewCalculatedMembers = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = True
        .RowAxisLayout xlCompactRow
    End With
    ActiveSheet.PivotTables("PivotTable2").PivotCache.RefreshOnFileOpen = False
    ActiveSheet.PivotTables("PivotTable2").RepeatAllLabels xlRepeatLabels
    With ActiveSheet.PivotTables("PivotTable2").CubeFields( _
        "[Range 1].[Lead Status]")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable2").CubeFields.GetMeasure _
        "[Range 1].[Lead Status]", xlCount, "Count of Lead Status"
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").CubeFields("[Measures].[Count of Lead Status]"), _
        "Count of Lead Status"
    ActiveSheet.PivotTables("PivotTable2").CompactLayoutRowHeader = "Lead Status"
    Range("F7").Select
    ActiveSheet.PivotTables("PivotTable2").DataPivotField.PivotItems( _
        "[Measures].[Count of Lead Status]").Caption = _
        "[Measures].[Count of Lead Status]"
    Range("F7").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

I have also highlighted the text in yellow where it halts assuming thats where the error lies, if someone could please help me would really appreciate it. Thanks

 

[qzyvxk]

Any help would be highly appreciated....

 

[RoryA]

Is the data in the same workbook as the pivot?

 

[qzyvxk]

Data is Sheet 2 (Dump) & Pivot to be display on Sheet 1(Daily Dump). I don't mind changing/renaming Sheet 1 name. If this helps you help me get to a solution.

 

[SpillerBD]

The Pivot Table name is likely already in use.
You Can Test that by changing the name.
There is a check that could be run, but I don't in mine since the Pivot Tables are supposed to be created only once, and the error triggers an end routine for me.

 

[bobbysmith]

try adding this
ActiveSheet.PivotTables("pivottable1").TableRange2.Clear

before
ActiveWorkbook.PivotCaches.create(SourceType:=xlExternal, SourceData:= _
ActiveWorkbook.Connections("WorksheetConnection_Dump!$D$1:$L$2000"), Version _
:=6).CreatePivotTable TableDestination:="Daily!R7C2", TableName:= _
"PivotTable1", DefaultVersion:=6