Invalid procedure call or argument

tazeo

tazeo

Board Regular
Joined
Feb 15, 2007
Messages
132
Office Version
  1. 365
Platform
  1. Windows
Got a strange one with this error:
cqmPhm.jpg


Doesn't happen all the time, but certainly happens for different people on different PCs. Macro is simply creating a Pivot table when the error hits.

VBA Code: 
    Sheets("Paste").Select
    Cells.Select
    Selection.Copy
    Sheets("Cleaned Data").Select
    Cells.Select
    Range("H1").Activate
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=9
    ActiveWindow.LargeScroll ToRight:=-2
    ActiveWindow.SmallScroll Down:=-39
    Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Application.CutCopyMode = False
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        UsedRange, Version:=7).CreatePivotTable _
        TableDestination:="'OutPut'!R3C2", TableName:="PivotTable1", DefaultVersion _
        :=7
    Sheets("OutPut").Select
    Cells(3, 2).Select
    With ActiveSheet.PivotTables("PivotTable1")

When I go debug this is the bit that is highlighted:
VBA Code: 
  ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        UsedRange, Version:=7).CreatePivotTable _
        TableDestination:="OutPut!R3C2", TableName:="PivotTable1", DefaultVersion _
        :=7

I have absolutely no idea what is happening.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
Do you have UsedRange defined as a variable somewhere ?

The code below is invalid, so I don't understand why it ever works.
SourceData:= UsedRange
UsedRange is a property of a sheet and needs a sheet object eg Activesheet.UsedRange.
 
Upvote 0
Do you have UsedRange defined as a variable somewhere ?
Alex Blakenburg said:
The code below is invalid, so I don't understand why it ever works.
SourceData:= UsedRange
UsedRange is a property of a sheet and needs a sheet object eg Activesheet.UsedRange.
Click to expand...
Oopppss that was me playing/fiddling to try and work out what was going on. Original script is:
VBA Code: 
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Cleaned Data!R1C1:R1048576C11", Version:=7).CreatePivotTable _
        TableDestination:="OutPut!A3C2", TableName:="PivotTable1", DefaultVersion _
        :=7
 
Upvote 0
Is the code in the workbook and is everyone using the same workbook ?
Does everyone use Excel 2007 or higher ?
On a PC where it fails can you show me what the Sheet "Cleaned Data" looks like (preferably via XL2BB) immediately before the PivotCache line that fails runs.
 
Upvote 0
Alex Blakenburg said:
Is the code in the workbook and is everyone using the same workbook ?
Does everyone use Excel 2007 or higher ?
On a PC where it fails can you show me what the Sheet "Cleaned Data" looks like (preferably via XL2BB) immediately before the PivotCache line that fails runs.
Click to expand...
Thanks Alex
Yes every workbook is the same, and all 5 people running Office 365.
Cant do the whole sheet I get an overflow error. But this i where the data is:
CSM HSC Tool V2-5.xlsm
ABCDEFGHIJKL
1Incident NumberIncident DateIncident StatusIncident ClassificationEvent TypeIncident TitleIncident Detailed DescriptionArea of ResponsibilityCity (Suburb)Level 03 Cost Centre DescriptionLevel 06 Cost Centre Description
25036865428/09/2021ClosedHEmployeeWHS-HZD-SCS-1Lorem ipsumNambourWoombyeQLDNORTH
35036831324/09/2021ClosedNEmployeeWHS-HZD-SCS-2Lorem ipsumPetrieKippa-RingQLDNORTH
45036790921/09/2021ClosedHEmployeeWHS-HZD-SCS-3Lorem ipsumNorthgateCarseldineQLDNORTH
55036788921/09/2021ClosedHEmployeeWHS-HZD-SCS-4Lorem ipsumCabooltureBurpengaryQLDNORTH
65036706813/09/2021ClosedHEmployeeWHS-HZD-SCS-5Lorem ipsumCabooltureNarangbaQLDNORTH
75036709013/09/2021ClosedHEmployeeWHS-HZD-SCS-6Lorem ipsumPetriePetrieQLDNORTH
85036700713/09/2021ClosedHEmployeeWHS-HZD-SCS-7Lorem ipsumPetrieLawntonQLDNORTH
95036684311/09/2021ClosedHEmployeeWHS-HZD-SCS-8Lorem ipsumNorthgateZillmereQLDNORTH
105036675309/09/2021ClosedIEmployeeWHS-HZD-SCS-9Lorem ipsumPetrieMango HillQLDNORTH
115036668709/09/2021ClosedIEmployeeWHS-HZD-SCS-10Lorem ipsumNorthgateNorthgateQLDNORTH
125036660708/09/2021ClosedIEmployeeWHS-HZD-SCS-11Lorem ipsumNambourGlass House MountainsQLDNORTH
135036659208/09/2021ClosedHEmployeeWHS-HZD-SCS-12Lorem ipsumSandgateSandgateQLDNORTH
145036637707/09/2021ClosedHEmployeeWHS-HZD-SCS-13Lorem ipsumReportingPetrieQLDNORTH
155036632406/09/2021ClosedHEmployeeWHS-HZD-SCS-14Lorem ipsumNorthgateNorthgateQLDNORTH
165036627106/09/2021ClosedHEmployeeWHS-HZD-SCS-15Lorem ipsumNorthgateGeebungQLDNORTH
175036620004/09/2021ClosedHEmployeeWHS-HZD-SCS-16Lorem ipsumCabooltureBurpengaryQLDNORTH
185036571531/08/2021ClosedNEmployeeWHS-HZD-SCS-17Lorem ipsumNorthgateNorthgateQLDNORTH
195036553528/08/2021ClosedNEmployeeWHS-HZD-SCS-18Lorem ipsumPetriePetrieQLDNORTH
205036834428/08/2021ClosedHEmployeeWHS-HZD-SCS-19Lorem ipsumNorthgateStrathpineQLDNORTH
215036542227/08/2021ClosedHEmployeeWHS-HZD-SCS-20Lorem ipsumCabooltureBurpengaryQLDNORTH
225036541127/08/2021ClosedHEmployeeWHS-HZD-SCS-21Lorem ipsumCabooltureNarangbaQLDNORTH
235036544027/08/2021ClosedHEmployeeWHS-HZD-SCS-22Lorem ipsumNorthgateStrathpineQLDNORTH
245036511825/08/2021ClosedHEmployeeWHS-HZD-SCS-23Lorem ipsumNorthgateStrathpineQLDNORTH
255036510924/08/2021ClosedHEmployeeWHS-HZD-SCS-24Lorem ipsumCabooltureBurpengaryQLDNORTH
265036484821/08/2021ClosedHEmployeeWHS-HZD-SCS-25Lorem ipsumCabooltureMorayfieldQLDNORTH
275036484321/08/2021ClosedHEmployeeWHS-HZD-SCS-26Lorem ipsumNorthgateZillmereQLDNORTH
285036484221/08/2021ClosedHEmployeeWHS-HZD-SCS-27Lorem ipsumNorthgateZillmereQLDNORTH
295036483720/08/2021ClosedHEmployeeWHS-HZD-SCS-28Lorem ipsumCabooltureMorayfieldQLDNORTH
305036449817/08/2021ClosedHEmployeeWHS-HZD-SCS-29Lorem ipsumSandgateBanyoQLDNORTH
315036437417/08/2021ClosedHEmployeeWHS-HZD-SCS-30Lorem ipsumNorthgateGeebungQLDNORTH
325036436316/08/2021ClosedHEmployeeWHS-HZD-SCS-31Lorem ipsumSandgateBanyoQLDNORTH
335036436216/08/2021ClosedHEmployeeWHS-HZD-SCS-32Lorem ipsumSandgateBanyoQLDNORTH
345036436615/08/2021ClosedIEmployeeWHS-HZD-SCS-33Lorem ipsumSandgateNudgeeQLDNORTH
355036420313/08/2021ClosedIEmployeeWHS-HZD-SCS-34Lorem ipsumCabooltureMorayfieldQLDNORTH
365036367509/08/2021ClosedHEmployeeWHS-HZD-SCS-35Lorem ipsumSandgateBoondallQLDNORTH
375036362507/08/2021ClosedHEmployeeWHS-HZD-SCS-36Lorem ipsumNorthgateNorthgateQLDNORTH
385036353705/08/2021ClosedNEmployeeWHS-HZD-SCS-37Lorem ipsumSandgateDeagonQLDNORTH
395036320430/07/2021ClosedNEmployeeWHS-HZD-SCS-38Lorem ipsumNambourBeerwahQLDNORTH
405036318130/07/2021ClosedNEmployeeWHS-HZD-SCS-39Lorem ipsumCabooltureCabooltureQLDNORTH
415036336628/07/2021ClosedIEmployeeWHS-HZD-SCS-40Lorem ipsumNorthgateZillmereQLDNORTH
425036472428/07/2021ClosedIEmployeeWHS-HZD-SCS-41Lorem ipsumNorthgateZillmereQLDNORTH
435036300228/07/2021ClosedHEmployeeWHS-HZD-SCS-42Lorem ipsumNorthgateVirginiaQLDNORTH
445036319626/07/2021ClosedIEmployeeWHS-HZD-SCS-43Lorem ipsumPetrieLawntonQLDNORTH
455036258925/07/2021ClosedHEmployeeWHS-HZD-SCS-44Lorem ipsumCabooltureBurpengaryQLDNORTH
465036246222/07/2021ClosedIEmployeeWHS-HZD-SCS-45Lorem ipsumNorthgateNorthgateQLDNORTH
475036237522/07/2021ClosedHEmployeeWHS-HZD-SCS-46Lorem ipsumPetrieBanyoQLDNORTH
485036228421/07/2021ClosedHEmployeeWHS-HZD-SCS-47Lorem ipsumPetrieLawntonQLDNORTH
495036217920/07/2021ClosedHEmployeeWHS-HZD-SCS-48Lorem ipsumNorthgateNorthgateQLDNORTH
505036218020/07/2021ClosedHEmployeeWHS-HZD-SCS-49Lorem ipsumNorthgateNorthgateQLDNORTH
515036189317/07/2021ClosedHEmployeeWHS-HZD-SCS-50Lorem ipsumPetriePetrieQLDNORTH
525036193817/07/2021ClosedHEmployeeWHS-HZD-SCS-51Lorem ipsumNambourNambourQLDNORTH
535036172915/07/2021ClosedIEmployeeWHS-HZD-SCS-52Lorem ipsumPetriePetrieQLDNORTH
545036162513/07/2021ClosedHEmployeeWHS-HZD-SCS-53Lorem ipsumPetriePetrieQLDNORTH
555036154713/07/2021ClosedHEmployeeWHS-HZD-SCS-54Lorem ipsumNorthgateVirginiaQLDNORTH
565036134911/07/2021ClosedHEmployeeWHS-HZD-SCS-55Lorem ipsumNorthgateNorthgateQLDNORTH
575036134610/07/2021ClosedHEmployeeWHS-HZD-SCS-56Lorem ipsumNorthgateNorthgateQLDNORTH
585036079905/07/2021ClosedNEmployeeWHS-HZD-SCS-57Lorem ipsumNorthgateNorthgateQLDNORTH
595036074701/07/2021ClosedHEmployeeWHS-HZD-SCS-58Lorem ipsumNambourNambourQLDNORTH
605036060001/07/2021ClosedHEmployeeWHS-HZD-SCS-59Lorem ipsumCabooltureCabooltureQLDNORTH
61
62
63
64
65
66
67
68
69
Cleaned Data
 
Upvote 0
See if this fixes the issue.

Replace the code that you sent through in your first post with this.

Note: This currently creating the pivot on the OutPut sheet at B3, change that to whatever you want it to be.
Set outRng = Worksheets("OutPut").Range("B3")​

VBA Code: 
Sub Test_OP()

    Sheets("Paste").Range("A1").CurrentRegion.Copy _
            Destination:=Sheets("Cleaned Data").Range("A1")
    
    Sheets("Cleaned Data").Activate
    Range("A1").Select

    Application.CutCopyMode = False

    Dim srcRng As Range
    Dim outRng As Range
    
    Set srcRng = Worksheets("Cleaned Data").Range("A1").CurrentRegion
    Set outRng = Worksheets("OutPut").Range("B3")
    
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=srcRng, Version:=7).CreatePivotTable _
        TableDestination:=outRng, TableName:="PivotTable1", DefaultVersion:=7

    Sheets("OutPut").Select
    Cells(3, 2).Select
    With ActiveSheet.PivotTables("PivotTable1")
    
End Sub
 
Upvote 0
Solution
Alex Blakenburg said:
See if this fixes the issue.

Replace the code that you sent through in your first post with this.

Note: This currently creating the pivot on the OutPut sheet at B3, change that to whatever you want it to be.
Set outRng = Worksheets("OutPut").Range("B3")​

VBA Code: 
Sub Test_OP()

    Sheets("Paste").Range("A1").CurrentRegion.Copy _
            Destination:=Sheets("Cleaned Data").Range("A1")
   
    Sheets("Cleaned Data").Activate
    Range("A1").Select

    Application.CutCopyMode = False

    Dim srcRng As Range
    Dim outRng As Range
   
    Set srcRng = Worksheets("Cleaned Data").Range("A1").CurrentRegion
    Set outRng = Worksheets("OutPut").Range("B3")
   
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=srcRng, Version:=7).CreatePivotTable _
        TableDestination:=outRng, TableName:="PivotTable1", DefaultVersion:=7

    Sheets("OutPut").Select
    Cells(3, 2).Select
    With ActiveSheet.PivotTables("PivotTable1")
   
End Sub
Click to expand...
Brilliant
 
Upvote 0
tazeo said:
Follow on Question, any thoughts on why this happen for different users?
Click to expand...
The only way I can get the same error is to put in an invalid number where either of the two 7s appear.
If it is still causing an issue on some computers, my understanding it that the Version parts are optional, so try it without that in the line as per the below:-

VBA Code: 
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=srcRng).CreatePivotTable _
        TableDestination:=outRng, TableName:="PivotTable1"
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Recording macro pivot table error
Replies
2
Views
970
biglb79
B
tezza
  • Solved
VBA code breaks if lines are in excess of 40,000 approx
2
Replies
12
Views
659
Alex Blakenburg
A
U
  • Solved
Re-organizing pivot table fields in vba
Replies
2
Views
579
ummjay
U
T
  • Question
VBA Dynamic PivotTable Name
Replies
4
Views
479
th259
T
S
  • Solved
VBA to create pivot tables
2
Replies
12
Views
1K
Domenic
D

Forum statistics

Threads
1,214,515
Messages
6,119,973
Members
448,933
Latest member
Bluedbw

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