Pivot Table Macro

[DesStratos]

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.

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

 

[Domenic]

Try adding single quotes ( ' ) around your sheet names...

SourceData:="'Current Month'!R1C1:R1000C4"

and

TableDestination:="'Current Month Count'!R1C1"

 

[Domenic]

Here's another approach to creating a pivot table. It creates the pivot table in two separate steps. It first creates a pivot cache from the source range, and then it uses the pivot cache to create a pivot table.

Sub Count_WDs()

    Dim source_range As Range
    Set source_range = Worksheets("Current Month").Range("A1").CurrentRegion
   
    Dim destination_sheet As Worksheet
    Set destination_sheet = Worksheets("Current Month Count")
   
    On Error Resume Next
    destination_sheet.PivotTables(1).TableRange2.Clear 'assumes that there's only one pivot table
    On Error GoTo 0
   
    Dim pivot_cache As PivotCache
    Set pivot_cache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=source_range.Address(External:=True))
   
    Dim pivot_table As PivotTable
    Set pivot_table = destination_sheet.PivotTables.Add( _
        PivotCache:=pivot_cache, _
        TableDestination:=destination_sheet.Range("A1"), TableName:="PivotTable7") 'change the pivot table name as desired or leave it out and accept the default name
   
    With pivot_table
        'etc
        '
        '
    End With
   
End Sub

Hope this helps!