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

V

VBAProIWish

Well-known Member
Joined
Jul 6, 2009
Messages
1,027
Office Version
  1. 365
Platform
  1. Windows
Hello All,

I'm trying to record a macro that inserts a pivot table from data from one worksheet onto a newly created worksheet, but I'm getting this error here...

"runtime 5"

"Invalid procedure call or argument"
Click to expand...


The code in RED is the code below that it bugs on.

Rich (BB code): 
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets("Sheet2").Name = "Cht-P"
    Sheets("DB-P").Select
    Range("A1").Select
    Selection.CurrentRegion.Select
   ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
       "DB-P!R1C1:R6674C5", Version:=xlPivotTableVersion12).CreatePivotTable _
       TableDestination:="Cht-P!R1C1", TableName:="PivotTable1", _
       DefaultVersion:=xlPivotTableVersion12
    Sheets("Cht-P").Select
    Cells(1, 1).Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'Cht-P!$A$1:$C$18")
    ActiveWorkbook.ShowPivotChartActiveFields = True
    ActiveChart.ChartType = xl3DColumnClustered
    ActiveWorkbook.ShowPivotChartActiveFields = False

Can anyone help me fix this?

Thanks much!
 
Sort by date Sort by votes
Please post more of the code prior to that part.
 
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
RoryA said:
Please post more of the code prior to that part.
Click to expand...
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:
Upvote 0
That doesn't contain the code you posted previously??
 
Upvote 0
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:
Upvote 0
Please learn to use code tags when posting code.

Are you making exactly the same pivot table each time, just changing the output sheet?
 
Upvote 0
RoryA said:
Please learn to use code tags when posting code.

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

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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
I am having the same issue:

VBA Code: 
Sub Create_Pivot_Table_1()


Dim LastRow As Long, LastColumn As Long
Dim DataRange As Range
Dim PTCache As PivotCache
Dim PT As PivotTable

Set wb = ThisWorkbook
Set wsData = ThisWorkbook.Worksheets("Enter CI & PL info")

Call Delete_PT_Sheet

With wsData
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
LastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column

Set DataRange = Range(.Cells(1, 1), .Cells(LastRow, LastColumn))

Set wsPT = wb.Worksheets.Add
wsPT.Name = "Pivot Table"

Set PTCache = wb.PivotCaches.Create(xlDatabase, DataRange)

  Set PT = PTCache.CreatePivotTable(wsPT.Cells("B5"), "PivotTable")
  With PT
     '//Pivot Table Layout Settings
     .RowAxisLayout xlTabularRow
    
     .TableStyle2 = "PivotStyleDark8"
     .HasAutoFormat = False
     .SubtotalHiddenPageItems = False
         
     '// Filter(s)
     With .PivotFields("Total Unit Value")
        .Orientation = xlPageField
        .EnableMultiplePageItems = False
     End With
    
     With .PivotFields("Line #")
        .Orientation = xlPageField
        .EnableMultiplePageItems = False
     End With
    
     '// Row Section(Layer1)
     With .PivotFields("Description of Goods")
        .Orientation = xlRowField
        .Position = 1
        .LayoutForm = xlTabular
        .Caption = "Item"
     End With

     With .PivotFields("HSCode")
        .Orientation = xlRowField
        .Position = 2
        .LayoutForm = xlTabular
        .Caption = "HSCode"
     End With
            
     '// Column Section
     With .PivotFields("Qty (pcs)")
        .Orientation = xlDataField
        .Position = 1
        .Caption = "Qty"
     End With
           
     With .PivotFields("Total Unit Value")
        .Orientation = xlDataField
        .Position = 2
        .Function = xlSum
        .Caption = "Unit Value"
     End With
        
     With .PivotFields("NW (kgs)")
        .Orientation = xlDataField
        .Position = 3
        .Caption = "NW"
     End With
        
     With .PivotFields("CBM")
        .Orientation = xlDataField
        .Position = 4
        .Caption = "CBM "
     End With
'PT.ColumnGrand = True
'PT.RowGrand = True
   End With
          
 End With


Run time error occur
[CODE=vba]  Set PT = PTCache.CreatePivotTable(wsPT.Cells("B5"), "PivotTable")
  With PT
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Pivot Table Macro
Replies
4
Views
429
SCPbrito
S
S
  • Solved
Having trouble with pivot table macro
2
Replies
12
Views
260
RoryA
RoryA
C
  • Solved
previous xlsb macro not working in xlsm workbook
Replies
3
Views
355
craigexcel
C
B
  • Solved
Recording macro pivot table error
Replies
2
Views
1K
biglb79
B
AOB
  • Question Question
Workbook.SlicerCaches.Add : won't work with late-binding (Invalid procedure call or argument)
Replies
0
Views
655
AOB
AOB

Forum statistics

Threads
1,224,505
Messages
6,179,152
Members
452,891
Latest member
JUSTOUTOFMYREACH

Share this page

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
Back
Top