[xpost][/xpost]
https://www.excelforum.com/excel-programm…tml#post5417674
https://www.mrexcel.com/board/threads/…rror-5.1149917/
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?
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
Display More
Assistance wld be more than appreciated, I am desperate already to get this working