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

Dump4Free

New Member
Joined
Jul 22, 2010
Messages
4
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.

Code:
 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.

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

New Member
Joined
Jul 22, 2010
Messages
4
This forum needs more categories.. My post was on like the 12th page already.. No one will ever see it there..
 

agrawal.meet

New Member
Joined
Jun 30, 2010
Messages
38
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

New Member
Joined
Jul 22, 2010
Messages
4
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.
 
Last edited:

Dump4Free

New Member
Joined
Jul 22, 2010
Messages
4
I have tried changing all the sheet and table names after i ran it the first time and still nothing.
 

Forum statistics

Threads
1,082,638
Messages
5,366,699
Members
400,914
Latest member
anandkb

Some videos you may like

This Week's Hot Topics

Top