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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,121
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Please post more of the code prior to that part.
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Daksh009

New Member
Joined
Jan 17, 2018
Messages
4
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 a moderator:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,121
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
That doesn't contain the code you posted previously??
 

Daksh009

New Member
Joined
Jan 17, 2018
Messages
4
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 a moderator:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,121
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Please learn to use code tags when posting code.

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

Daksh009

New Member
Joined
Jan 17, 2018
Messages
4
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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,121
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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
 

barcode_11

New Member
Joined
Feb 28, 2018
Messages
12
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,260
Messages
5,485,728
Members
407,510
Latest member
Tryintouseexcel

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top