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.
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
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
Last edited by a moderator: