Pivot table creation fails with error "Invalid Procedure or argument call"

dg1069 · #1 (**permalink**) 09-07-10, 08:27

Hi,

I am trying to create a pivot table to a new sheet in excel 2003, using the following code

Code:

Set WS = Sheets.Add
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"InputSheet!R1C1:R20C20").CreatePivotTable TableDestination:="WS!R1C1", _
        TableName:="MyPivotTable", DefaultVersion:= _
        xlPivotTableVersion11

I have also tried removing Sheets.Add & using

Code:

TableDestination:=""

But both of the above fails.

Please help!!!

Colin Legg · #2 (**permalink**) 09-07-10, 18:09

Hi,

WS is a reference to a worksheet rather than the worksheet's name, so your TableDestination argument should be:

Code:

TableDestination:=WS.Name &  "!R1C1"

As a general rule it is more robust to include ' in case there is a space in the name.

Code:

TableDestination:="'" & WS.Name &  "'!R1C1"

Don't try to do too much in one line of code. It makes it more readable and managable if you split it out into simpler steps, for example:

Code:

Sub foo()
    Dim WS As Worksheet
    Dim PC As PivotCache
    Dim PT As PivotTable
 
    Set WS = ActiveWorkbook.Worksheets.Add
 
    Set PC = ActiveWorkbook.PivotCaches.Add( _
                    SourceType:=xlDatabase, _
                    SourceData:="InputSheet!R1C1:R20C20")
 
    Set PT = PC.CreatePivotTable( _
                    TableDestination:="'" & WS.Name &  "'!R1C1", _
                    TableName:="MyPivotTable", _
                    DefaultVersion:=xlPivotTableVersion11)
 
End Sub

For example, now it's easier to debug or add defensive coding/error handling if there's a problem .

Hope that helps...

dg1069 · #3 (**permalink**) 09-08-10, 01:14

Thanks for your response. I would follow your advice hence forth for easy debugging.

Also I finally got the problem solved with TableDestination:="" and DefaultVersion:=xlPivotTableVersion10.

Earlier when I tried with TableDestination:="", it was throwing error because of the wrong version it seems.

I will try your version of the code, as that seems more effective.

Internet Services

Web Development

Digital Marketing

Consumer Tech