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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,963
Members
413,954
Latest member
mrsandy

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
Top