Invalid procedure call or argument

[barmstr6]

I need some help. I have created a simple macro that creates a pivot table. It works fine on my computer, but other users get an error. Below is what is highlighted for users that receive the error.

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R2C5:R1048576C5", Version:=7).CreatePivotTable TableDestination:= _
        "Sheet2!C9", TableName:="PivotTable4", DefaultVersion:=7

Here is the entire macro. Its not very complicated, but running into the issue for others to use.

Sub layers()
'
' layers Macro
'

'
    Sheets("Sheet1").Select
    Sheets("Sheet2").Visible = True
    Sheets("Sheet1").Select
    Columns("E:E").Select
    Range("E2").Activate
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Range("H17").Select
    ActiveWindow.SmallScroll Down:=-72
    Sheets("Sheet2").Select
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],C[8]:C[9],2,FALSE),0)"
    Columns("I:M").Select
    Selection.Delete Shift:=xlToLeft
    Sheets("Sheet1").Select
    Columns("E:E").Select
    Range("E2").Activate
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R2C5:R1048576C5", Version:=7).CreatePivotTable TableDestination:= _
        "Sheet2!C9", TableName:="PivotTable4", DefaultVersion:=7
    Sheets("Sheet2").Select
    Cells(1, 9).Select
    With ActiveSheet.PivotTables("PivotTable4")
        .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("PivotTable4").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable4").RepeatAllLabels xlRepeatLabels
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("New Quantitiy"), "Sum of New Quantitiy", xlSum
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("New Quantitiy")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Sum of New Quantitiy")
        .Caption = "Count of New Quantitiy"
        .Function = xlCount
    End With
    Columns("I:J").Select
    Selection.Copy
    Columns("L:L").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("L:M").EntireColumn.AutoFit
    Columns("L:M").EntireColumn.AutoFit
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],C[8]:C[9],2,FALSE),0)"
    Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D35"), Type:=xlFillDefault
    Range("D2:D35").Select
    ActiveWindow.SmallScroll Down:=-54
    Sheets("Sheet2").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Sheet1").Select
    Range("I2").Select
    Sheets("Sheet1").Select
    Range("I2").Select
End Sub

 

[RoryA]

They probably have a different version of Office. Remove the Version:=7 and DefaultVersion:=7 arguments.