Recording a Macro but it doesnt work/run - Runtime Error 5

[Staal]

Hi, pls forgive me for just asking like this, I am new here and in desperate need of help on a Excel Marco issue. Im trying to run a macro that inserts a pivot table into a sheet but when I run the macro after recording it I get a runtime 5 error. I have tried everything I can and cant get it working... pls cld you assist?

Sub SixtyPivot()
'
' SixtyPivot Macro
'

'
    Sheets("60+").Select
    Cells.Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "60+!R1C1:R1048576C105", Version:=6).CreatePivotTable TableDestination:= _
        "Pivot 60+!R1C1", TableName:="PivotTable1", DefaultVersion:=6
    Sheets("Pivot 60+").Select
    Cells(1, 1).Select
    ActiveWindow.SmallScroll Down:=0
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Insured")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Insured")
        .PivotItems("(blank)").Visible = False
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("NetEstimate")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("NetEstimate")
        .PivotItems("(blank)").Visible = False
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Status")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Status")
        .PivotItems("(blank)").Visible = False
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("2020/10/31")
        .Orientation = xlRowField
        .Position = 4
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("2020/10/31")
        .PivotItems("(blank)").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Insured"), "Count of Insured", xlCount
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("NetEstimate"), "Count of NetEstimate", xlCount
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Status"), "Count of Status", xlCount
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of NetEstimate")
        .Caption = "Sum of NetEstimate"
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Status")
        .Caption = "Sum of Status"
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Status")
        .Caption = "Count of Status"
        .Function = xlCount
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("2020/10/31"), "Count of 2020/10/31", xlCount
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Status")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of 2020/10/31")
        .Caption = "Sum of 2020/10/31"
        .Function = xlSum
    End With
    Columns("C:C").Select
    Selection.NumberFormat = "$ #,##0.00"
    Columns("C:C").Select
    Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
    Range("F12").Select
End Sub

Assistance wld be more than appreciated

 

[Fluff]

Cross posted Macro Recorder to insert Pivot Table .. gives Runtime 5 error after recording it and runni
And Macro Recorder to insert Pivot Table .. gives Runtime 5 error after recording it and running Macro - OzGrid Free Excel/VBA Help Forum

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.

 

[Domenic]

Since the name of the destination sheet contains spaces, you'll need to enclose the name within single quotes...

TableDestination:= "'Pivot 60+'!R1C1"

Note, you'll also get an error if you try creating a pivot table where the destination already contains a pivot table or the worksheet already has pivot table with the same name.

Hope this helps!