Pivot Table VBA

[chaddres]

I get Run-time error '5'" Invalid procedure call or argument when I try to run this macro. I want to Click on A1 and select the data in the worksheet (so I click CTRL* to select it all). Then, line 3 of my code gives me the error. I want to place a Pivot Table in cell J2 on the Time Export worksheet. Any help is appreciated!

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A1").Select
    Selection.CurrentRegion.Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Time Export!R1C1:R1695C8", Version:=7).CreatePivotTable TableDestination:= _
        "Time Export!R2C10", TableName:="PivotTable1", DefaultVersion:=7
    Cells(2, 10).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
        .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("PivotTable1").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Resource")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Customer Hours/ Units"), _
        "Sum of Customer Hours/ Units", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Customer Hours/ Units2"), _
        "Sum of Customer Hours/ Units2", xlSum
End Sub

 

[RoryA]

Which version of Excel are you running it on, and where did you get 7 as the version number?

 

[chaddres]

I am running Office 365. I recorded the macro and then tried to use it after I recorded it.

 

[RoryA]

Try using this:

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "'Time Export'!R1C1:R1695C8").CreatePivotTable TableDestination:= _
        "'Time Export'!R2C10", TableName:="PivotTable1"

Note there are single quotes around both instances of the sheet name since it has a space in it, and that I have removed the version arguments.

 

[chaddres]

This worked! Thank you!