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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
This forum needs more categories.. My post was on like the 12th page already.. No one will ever see it there..
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top