Macro to Create Pivot Table on Existing WorkSheet

qzyvxk

New Member
Joined
Oct 27, 2018
Messages
3
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.
Code:
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
 
Last edited by a moderator:

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Is the data in the same workbook as the pivot?
 
Upvote 0
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. ;-)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top