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

Staal

New Member
Joined
Oct 31, 2020
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
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?



VBA Code:
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,903
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,403
Since the name of the destination sheet contains spaces, you'll need to enclose the name within single quotes...

VBA Code:
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,800
Messages
5,574,398
Members
412,590
Latest member
Velly
Top