DesStratos
New Member
- Joined
- Oct 8, 2014
- Messages
- 12
Good Morning All,
I am trying to write a macro that deletes an existing pivot table, then creates a new one based on another tab.
For some reason, even though when writing it the table generates fine, when I run the macro it deletes the old one then errors "Invalid Proceedure call or argument"
Its been a while since I used Pivot Tables or Macros so I am a little rusty.
Any ideas? Here is my code, this wasn't manually written but done with the Macro Recording Feature. The data is in a tab called "Current Month" and the data will vary in total rows each month, so I set a range of 1000 rows to ensure all data was always captured.
I am trying to write a macro that deletes an existing pivot table, then creates a new one based on another tab.
For some reason, even though when writing it the table generates fine, when I run the macro it deletes the old one then errors "Invalid Proceedure call or argument"
Its been a while since I used Pivot Tables or Macros so I am a little rusty.
Any ideas? Here is my code, this wasn't manually written but done with the Macro Recording Feature. The data is in a tab called "Current Month" and the data will vary in total rows each month, so I set a range of 1000 rows to ensure all data was always captured.
VBA Code:
Sub Count_WDs()
'
' Count_WDs Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Current Month!R1C1:R1000C4", Version:=6).CreatePivotTable TableDestination _
:="Current Month Count!R1C1", TableName:="PivotTable7", DefaultVersion:=6
Sheets("Current Month Count").Select
Cells(1, 1).Select
With ActiveSheet.PivotTables("PivotTable7")
.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("PivotTable7").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable7").RepeatAllLabels xlRepeatLabels
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("'Current Month Count'!$A$1:$C$18")
ActiveChart.Parent.Delete
With ActiveSheet.PivotTables("PivotTable7").PivotFields("PSM")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable7").AddDataField ActiveSheet.PivotTables( _
"PivotTable7").PivotFields("PSM"), "Count of PSM", xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub