Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2010
    Posts
    13

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

    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!!!

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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...

  3. #3
    Join Date
    Apr 2010
    Posts
    13
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •