Runtime error 5, Invalid Procedure call or argument error when creating a pivot table
Page 3 of 3 FirstFirst 123
Results 21 to 28 of 28

Thread: Runtime error 5, Invalid Procedure call or argument error when creating a pivot table
Thanks Thanks: 0 Likes Likes: 0

  1. #21
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,661
    Post Thanks / Like
    Mentioned
    47 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Runtime error 5, Invalid Procedure call or argument error when creating a pivot table

    Please post more of the code prior to that part.

  2. #22
    New Member
    Join Date
    Jan 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Runtime error 5, Invalid Procedure call or argument error when creating a pivot table

    Quote Originally Posted by RoryA View Post
    Please post more of the code prior to that part.
    Code:
    Sub createPivotTablePreI_C()
    
    
        'declare variables to hold row and column numbers that define source data cell range
        Dim myFirstRow As Long
        Dim myLastRow As Long
        Dim myFirstColumn As Long
        Dim myLastColumn As Long
    
    
        'declare variables to hold source and destination cell range address
        Dim mySourceData As String
        Dim myDestinationRange As String
    
    
        'declare object variables to hold references to source and destination worksheets, and new Pivot Table
        Dim mySourceWorksheet As Worksheet
        Dim myDestinationWorksheet As Worksheet
        Dim myPivotTable As PivotTable
    
    
        'identify source and destination worksheets
        With ActiveWorkbook
            Set mySourceWorksheet = .Worksheets("Report")
            Set myDestinationWorksheet = .Worksheets("SAP PRE I&C")
        End With
    
    
        'obtain address of destination cell range
        myDestinationRange = myDestinationWorksheet.Range("A1").Address(ReferenceStyle:=xlR1C1)
    
    
        'identify first row and first column of source data cell range
        myFirstRow = 1
        myFirstColumn = 1
    
    
        With mySourceWorksheet.Cells
    
    
            'find last row and last column of source data cell range
            myLastRow = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            myLastColumn = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    
    
            'obtain address of source data cell range
            mySourceData = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)).Address(ReferenceStyle:=xlR1C1)
    
    
        End With
    
    
        'create Pivot Table cache and create Pivot Table report based on that cache
        Set myPivotTable = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=ActiveWorkbook.Worksheets("Report").Range(ActiveWorkbook.Worksheets("REPORT").Cells(myFirstRow, myFirstColumn), _
        ActiveWorkbook.Worksheets("REPORT").Cells(myLastRow, myLastColumn)) _
        .Address(ReferenceStyle:=xlR1C1)) _
        .createPivotTable(TableDestination:=ActiveWorkbook.Worksheets("SAP POST I&C").Cells(1, 1), _
        TableName:="PivotTableExistingSheet")
    
    
        'add, organize and format Pivot Table fields
        With myPivotTable
            .PivotFields("SAP ID").Orientation = xlRowField
            With .PivotFields("SAP ID")
                .Orientation = xlDataField
                .Position = 1
            End With
            
        End With
        With myPivotTable
                    .PivotFields("R4G").Orientation = xlColumnField
       End With
    
    
    End Sub
    Last edited by RoryA; Jan 17th, 2018 at 05:33 AM. Reason: Code tags

  3. #23
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,661
    Post Thanks / Like
    Mentioned
    47 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Runtime error 5, Invalid Procedure call or argument error when creating a pivot table

    That doesn't contain the code you posted previously??

  4. #24
    New Member
    Join Date
    Jan 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Runtime error 5, Invalid Procedure call or argument error when creating a pivot table

    I had edited the code changing .sheets to activeworkbook.sheet--
    Problem i am facing is that though the code works fine for other sheet in same workbook with source data but fails to work again if i wish to have the pivot data in another sheet. hope i am clear with the question .

    to simplify
    sheet 1 has source data
    sheet 2 is first pivot
    nd i need another pivot of sheet 1 in sheet 3.

    request for help where i can change destinatin sheet address and can get 2 or more pivot sheets of same source.
    current moving cursor over mypivottable shows nothing

    HERE Is the full code. when one pivot works other fails

    and damm i m frustrated editing
    Code:
    Sub createPivotTablePostI_C()
    
    
        'declare variables to hold row and column numbers that define source data cell range
        Dim myFirstRow As Long
        Dim myLastRow As Long
        Dim myFirstColumn As Long
        Dim myLastColumn As Long
    
    
        'declare variables to hold source and destination cell range address
        Dim mySourceData As String
        Dim myDestinationRange As String
    
    
        'declare object variables to hold references to source and destination worksheets, and new Pivot Table
        Dim mySourceWorksheet As Worksheet
        Dim myDestinationWorksheet As Worksheet
        Dim myPivotTable As PivotTable
    
    
        'identify source and destination worksheets
        With ActiveWorkbook
            Set mySourceWorksheet = .Worksheets("Report")
            Set myDestinationWorksheet = .Worksheets("SAP POST I&C")
        End With
    
    
        'obtain address of destination cell range
        myDestinationRange = myDestinationWorksheet.Range("A1").Address(ReferenceStyle:=xlR1C1)
    
    
        'identify first row and first column of source data cell range
        myFirstRow = 1
        myFirstColumn = 1
    
    
        With mySourceWorksheet.Cells
    
    
            'find last row and last column of source data cell range
            myLastRow = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            myLastColumn = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    
    
            'obtain address of source data cell range
            mySourceData = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)).Address(ReferenceStyle:=xlR1C1)
    
    
        End With
    
    
        'create Pivot Table cache and create Pivot Table report based on that cache
        Set myPivotTable = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=ActiveWorkbook.Worksheets("Report").Range(ActiveWorkbook.Worksheets("REPORT").Cells(myFirstRow, myFirstColumn), _
        ActiveWorkbook.Worksheets("REPORT").Cells(myLastRow, myLastColumn)) _
        .Address(ReferenceStyle:=xlR1C1)) _
        .createPivotTable(TableDestination:=ActiveWorkbook.Worksheets("SAP POST I&C").Cells(1, 1), _
        TableName:="PivotTableExistingSheet")
    
    
        'add, organize and format Pivot Table fields
        With myPivotTable
            .PivotFields("SAP ID").Orientation = xlRowField
            With .PivotFields("SAP ID")
                .Orientation = xlDataField
                .Position = 1
            End With
            
        End With
        With myPivotTable
                    .PivotFields("R4G").Orientation = xlColumnField
       End With
    
    
    End Sub
    
    
    Sub createPivotTablePreI_C()
    
    
        'declare variables to hold row and column numbers that define source data cell range
        Dim myFirstRow As Long
        Dim myLastRow As Long
        Dim myFirstColumn As Long
        Dim myLastColumn As Long
    
    
        'declare variables to hold source and destination cell range address
        Dim mySourceData As String
        Dim myDestinationRange As String
    
    
        'declare object variables to hold references to source and destination worksheets, and new Pivot Table
        Dim mySourceWorksheet As Worksheet
        Dim myDestinationWorksheet As Worksheet
        Dim myPivotTable As PivotTable
    
    
        'identify source and destination worksheets
        With ActiveWorkbook
            Set mySourceWorksheet = .Worksheets("Report")
            Set myDestinationWorksheet = .Worksheets("SAP PRE I&C")
        End With
    
    
        'obtain address of destination cell range
        myDestinationRange = myDestinationWorksheet.Range("A1").Address(ReferenceStyle:=xlR1C1)
    
    
        'identify first row and first column of source data cell range
        myFirstRow = 1
        myFirstColumn = 1
    
    
        With mySourceWorksheet.Cells
    
    
            'find last row and last column of source data cell range
            myLastRow = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            myLastColumn = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    
    
            'obtain address of source data cell range
            mySourceData = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)).Address(ReferenceStyle:=xlR1C1)
    
    
        End With
    
    
        'create Pivot Table cache and create Pivot Table report based on that cache
        
    Set myPivotTable = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=ActiveWorkbook.Worksheets("Report").Range(ActiveWorkbook.Worksheets("REPORT").Cells(myFirstRow, myFirstColumn), _
        ActiveWorkbook.Worksheets("REPORT").Cells(myLastRow, myLastColumn)) _
        .Address(ReferenceStyle:=xlR1C1)) _
        .createPivotTable(TableDestination:=ActiveWorkbook.Worksheets("SAP PRE I&C").Cells(1, 1), _
        TableName:="PivotTableExistingSheet")
        
        'add, organize and format Pivot Table fields
        With myPivotTable
            .PivotFields("SAP ID").Orientation = xlRowField
            With .PivotFields("SAP ID")
                .Orientation = xlDataField
                .Position = 1
            End With
            
        End With
        With myPivotTable
                    .PivotFields("R4G").Orientation = xlColumnField
       End With
    
    
    End Sub
    Last edited by RoryA; Jan 17th, 2018 at 05:54 AM. Reason: Code tags

  5. #25
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,661
    Post Thanks / Like
    Mentioned
    47 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Runtime error 5, Invalid Procedure call or argument error when creating a pivot table

    Please learn to use code tags when posting code.

    Are you making exactly the same pivot table each time, just changing the output sheet?

  6. #26
    New Member
    Join Date
    Jan 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Runtime error 5, Invalid Procedure call or argument error when creating a pivot table

    Quote Originally Posted by RoryA View Post
    Please learn to use code tags when posting code.

    Are you making exactly the same pivot table each time, just changing the output sheet?
    yep just changing output sheet .

    and apology for not tagging code proper. this was my first post here . had no available option to post new thread. nd had to work out this problem asap

  7. #27
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,661
    Post Thanks / Like
    Mentioned
    47 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Runtime error 5, Invalid Procedure call or argument error when creating a pivot table

    Try this:

    Code:
    Option Explicit
    Sub createPivotTablePostI_C()
        createPivotTable ActiveWorkbook.Worksheets("SAP POST I&C")
    End Sub
    
    Sub createPivotTablePreI_C()
        createPivotTable ActiveWorkbook.Worksheets("SAP PRE I&C")
    End Sub
    
    Sub createPivotTable(myDestinationWorksheet As Worksheet)
    
        'declare variables to hold source and destination cell range address
        Dim mySourceData As String
        Dim myDestinationRange As Range
    
    
        'declare object variables to hold references to source and destination worksheets, and new Pivot Table
        Dim mySourceWorksheet As Worksheet
        Dim myPivotCache As PivotCache
        Dim myPivotTable As PivotTable
    
    
        'identify source and destination worksheets
        With ActiveWorkbook
            Set mySourceWorksheet = .Worksheets("Report")
        End With
    
    
        'obtain address of destination cell range
        Set myDestinationRange = myDestinationWorksheet.Range("A1")
    
    
        With mySourceWorksheet
            'obtain address of source data cell range
            mySourceData = "." & .Name & "'!" & .Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)
    
        End With
    
    
        'create Pivot Table cache and create Pivot Table report based on that cache
        Set myPivotCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=mySourceData)
        Set myPivotTable = myPivotCache.createPivotTable(TableDestination:=myDestinationRange)
    
    
        'add, organize and format Pivot Table fields
        With myPivotTable
        
            With .PivotFields("SAP ID")
                .Orientation = xlDataField
                .Position = 1
            End With
            
            .PivotFields("SAP ID").Orientation = xlRowField
            .PivotFields("R4G").Orientation = xlColumnField
            
        End With
    
    End Sub

  8. #28
    New Member
    Join Date
    Feb 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Runtime error 5, Invalid Procedure call or argument error when creating a pivot table

    Hi, I have a code that converts an excel tab into a pdf with specific naming convention according to cell values. However when I lengthen the cell value in cell H23, the code gives gives a runtime error 5. In the same cell when I shorten the value, the code works perfectly. Not sure whats happening. Would really appreciate any help.
    (Shee1 = ABC)

    Code:
    Sub CreatePDF1()


    Application.ScreenUpdating = False


    Sheets("ABC").ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=ThisWorkbook.Path & "" & Format(Now, "dd.mmm.yy") & "_" & Sheet1.Range("F7").Value & "_" & Sheet1.Range("N22").Value & "_" & Sheet1.Range("H23").Value & ".pdf", _
    OpenAfterPublish:=True


    ActiveWorkbook.Sheets(1).Select


    Application.ScreenUpdating = True


    End Sub

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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