Run-time error '424' Object required

[chetanrangan2000]

Hi

I have written a Macro code to add certain column and formula and to add new sheet and in that new sheet a pivot table. When I try to run everything works well and gets stuck when creating a pivot table. I am getting Run Time error 424 "object required".

Below is macro code:

Sub VisaResolvedCasesMacro1()

Dim Pt As PivotTable
Dim cacheofPt As PivotCache
Dim Pf As PivotField
Dim Pi As PivotItem


    ActiveCell.Offset(0, 4).Columns("A:A").EntireColumn.Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "Cycle Time"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
    ActiveCell.Select
    Selection.NumberFormat = "0.00"
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A391")
    ActiveCell.Range("A1:A391").Select
    ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "Network Days"
    ActiveCell.Offset(0, -1).Columns("A:B").EntireColumn.Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveCell.Offset(1, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=NETWORKDAYS(RC[-3],RC[-2])-1-MOD(RC[-3],1)+MOD(RC[-2],1)"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A391")
    ActiveCell.Range("A1:A391").Select
    ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "Age Group"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]<=1, ""<=24H"",IF(RC[-1]<=2, ""24-48H"",IF(RC[-1]<=5, ""2-5 D"",IF(RC[-1]>5, ""Over 5D""))))"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A391")
    ActiveCell.Range("A1:A391").Select
    ActiveCell.Offset(-1, 25).Range("A1").Select
    ActiveCell.Offset(0, -8).Columns("A:A").EntireColumn.EntireColumn.AutoFit
    ActiveCell.Offset(0, -9).Columns("A:A").EntireColumn.EntireColumn.AutoFit
    ActiveCell.Offset(0, -7).Columns("A:A").EntireColumn.EntireColumn.AutoFit
    ActiveCell.Offset(0, -6).Columns("A:A").EntireColumn.EntireColumn.AutoFit
    ActiveCell.Offset(0, -5).Columns("A:A").EntireColumn.EntireColumn.AutoFit
    ActiveCell.Offset(0, -4).Columns("A:A").EntireColumn.EntireColumn.AutoFit
    ActiveCell.Offset(4, -8).Range("A1").Select
    Cells.Replace What:="5-Excellent", Replacement:="5", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="4-Above Average", Replacement:="4", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="3-Met Needs", Replacement:="3", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="2-Below Average", Replacement:="2", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="1-Unacceptable", Replacement:="1", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
    Sheets.Add
    
    Sheets("Sheet1").Select
        Set cacheofPt = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range("A1:AF1000"))
    
    Sheets("Sheet1").Select
        Set Pt = ActiveSheet.PivotTables.Add(cacheofPt, Range("A1"), "MyPt")
    
    With Pt
        .PivotFields("Case Sub Area 1").Orientation = xl.PageField
        .PivotFields("Case Division").Orientation = xl.RowField
        .PivotFields("Case Number").Orientation = xl.DataField
            .Caption = "Count of Case Numbers"
            .Function = xlCount
        .PivotFields("Cycle Time").Orientation = xl.DataField
            .Caption = "Avg of Cycle Time"
            .Function = xlAverage
            .NumberFormat = "0.0"
        .PivotFields("Question 5").Orientation = xl.DataField
            .Caption = "Avg. of Question 5"
            .Function = xlAverage
            .NumberFormat = "0.0"
    End With
    
            
    
End Sub