Run-time error 5 : Invalid procedure call or argument (1 Viewer)

[Naz]

Hi Everyone,

I have recorded a very simple Macro to pivot some data and when I run the macro I keep getting this error. When I click on Debug the yellow highlighted data below seems to stop it from running..

Any ideas what this means or how it can be rectified please?

 

[cheekybuddha]

Hi,

Try replacing the bracket between .Create and SourceType with a space.

 

[arnelgp]

did you check and delete previous PivotTables before creating new one:

    Dim xPT As PivotTable
    For Each xPT In Sheet2.PivotTables
        Sheet2.Range(xPT.TableRange2.Address).Delete Shift:=xlUp
    Next

 

[cheekybuddha]

Scratch that! I missed the second function call.

Try changing to:

' ...
With ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Sheet1!R1C1:R1048576C16", Version:=6)
  .CreatePivotTable TableDestination:="Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion:=6
End With
' ...

Are you sure "Sheet1!R1C1:R1048576C16" is a valid reference?

 

[Naz]

Hi,

Try replacing the bracket between .Create and SourceType with a space.

Thanks but that then creats a Syntx error.

 

[Naz]

Scratch that! I missed the second function call.

Try changing to:

' ...
With ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Sheet1!R1C1:R1048576C16", Version:=6)
  .CreatePivotTable TableDestination:="Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion:=6
End With
' ...

Are you sure "Sheet1!R1C1:R1048576C16" is a valid reference?

When I initially recorded the Macro I created a pivot table from data on Sheet1 and the pivot worked fine. I saved and closed.

 

[Naz]

Yes, I started again, still same thing. Maybe it's to do with how the Macro is saved? I saved it on the Workbook .

 

[Naz]

did you check and delete previous PivotTables before creating new one:

    Dim xPT As PivotTable
    For Each xPT In Sheet2.PivotTables
        Sheet2.Range(xPT.TableRange2.Address).Delete Shift:=xlUp
    Next

Yes

 

[cheekybuddha]

Can you test it like this:

Sub MACRO6()

  Dim rng AS Range

  Set rng = Me.Range("A1:N1048576")
  With ThisWorkbook
    .Worksheets.Add
    With .PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rng, Version:=6)
      .CreatePivotTable TableDestination:=.Worksheets("Sheet2").Range("A3"), TableName:="PivotTable1", DefaultVersion:=6
    End With
  End WIth
' etc ...

hth,

d

 

[Naz]

I think I know what the issue was. When a pivot is created it automatically creates a new sheet with a number but if that sheet number is already used it won't work. I recorded the pivot macro again and started it on a named sheet. So the Macro can only be run on that named sheet which must be blanked. It's strange because I remember always being able to rerun a macro even if the macro created a new sheet.

 

[cheekybuddha]

Thanks for sharing your solution.