Run-time error '5': Invalid procedure call or argument

[Dump4Free]

Okay. So i am having an issue with my macro that creates pivot tables.

Everything works fine the first time i run it but when i delete the pivot tables that were created and try to run it again i receive the error "Run-time error '5': Invalid procedure call or argument" one the second line.

 ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "RawData!R7C1:R70000C13", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="Sheet1!R3C1", TableName:="PivotTable3", DefaultVersion _
        :=xlPivotTableVersion12

I have placed watches everywhere to see if something was getting left behind from the first run but i cant seem to find anything. I am not actually the one that developed the code. The person that did is on vacation and I was asked to see if i can fix it.

Right now the only fix i have is closing excel and opening it again lol. Any help would be nice thanks.

Sub CreatePivotTables()
'
'
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "RawData!R7C1:R70000C13", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="Sheet1!R3C1", TableName:="PivotTable3", DefaultVersion _
        :=xlPivotTableVersion12
    Sheets("Sheet1").Select
    Cells(3, 1).Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("AccountNumber")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("RdgDate")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("RdgDate"), "Count of RdgDate", xlCount
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Count of RdgDate")
        .Caption = "Min of RdgDate"
        .Function = xlMin
    End With
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("RdgDate"), "Count of RdgDate", xlCount
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Count of RdgDate")
        .Caption = "Max of RdgDate"
        .Function = xlMax
    End With
    Range("B5").Select
    Selection.NumberFormat = "m/d/yyyy"
    Columns("B:C").Select
    Selection.NumberFormat = "m/d/yyyy"
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "PivotRange2"
    Sheets("RawData").Select
    Sheets.Add
    ActiveWorkbook.Worksheets("PivotRange2").PivotTables("PivotTable3").PivotCache. _
        CreatePivotTable TableDestination:="Sheet2!R3C1", TableName:="PivotTable4" _
        , DefaultVersion:=xlPivotTableVersion12
    Sheets("Sheet2").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("AccountNumber")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("RdgDate")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Hour")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Kwh")
        .Orientation = xlRowField
        .Position = 4
    End With
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("Kwh"), "Count of Kwh", xlCount
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Count of Kwh")
        .Caption = "Sum of Kwh"
        .Function = xlSum
    End With
    ActiveSheet.PivotTables("PivotTable4").RowAxisLayout xlOutlineRow
    ActiveSheet.PivotTables("PivotTable4").RowAxisLayout xlTabularRow
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "PivotHourly2"
    Range("D35").Select
    Sheets("RawData").Select
    ActiveWindow.SmallScroll Down:=-15
   End Sub

 

[Dump4Free]

This forum needs more categories.. My post was on like the 12th page already.. No one will ever see it there..

 

[agrawal.meet]

I am not sure but it could be due to the fact that you have hard coded the pivot table name. Try generating the name dynamically.

 

[Dump4Free]

How would i go about doing that?

Edit: I ran it once than manually changed the table name and tried to run it again.. Same error.

 

[Dump4Free]

I have tried changing all the sheet and table names after i ran it the first time and still nothing.