Runtime Error 5 in VBA: Invalid Procedure Call or Argument

Question

ActiveSheet.Name = "RReal+RCEmin" 
Dim UltimaFila As Long 
Dim RangoDatos As String 
UltimaFila = Cells(Rows.Count, 3).End(xlUp).Row 
RangoDatos = "RReal+RCEmin!R1C1:R" & UltimaFila & "C9" 
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=RangoDatos, Version:=xlPivotTableVersion8).CreatePivotTable TableDestination:="RReal+RCEmin!R1C15", TableName:="D1", DefaultVersion:=xlPivotTableVersion8

I have tried to create the table using every version but it does not work.

Answer 1

Try wrapping your sheet names within single quotes...

Dim SheetName As String
Dim UltimaFila As Long
Dim RangoDatos As String

SheetName = "RReal+RCEmin"

ActiveSheet.Name = SheetName

UltimaFila = Cells(Rows.Count, 3).End(xlUp).Row

RangoDatos = "'" & SheetName & "'!R1C1:R" & UltimaFila & "C9"

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=RangoDatos).CreatePivotTable tabledestination:="'" & SheetName & "'!R1C15", TableName:="D1"

Here's another way...

Dim SheetName As String
Dim RangoDatos As String
Dim UltimaFila As Long
Dim PTCache As PivotCache
Dim PT As PivotTable

SheetName = "RReal+RCEmin"

ActiveSheet.Name = SheetName

UltimaFila = Cells(Rows.Count, 3).End(xlUp).Row

RangoDatos = "'" & SheetName & "'!R1C1:R" & UltimaFila & "C9"

Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=RangoDatos)

Set PT = ActiveSheet.PivotTables.Add(PivotCache:=PTCache, tabledestination:="'" & SheetName & "'!R1C15", TableName:="D1")

As you can see, it creates the PivotCache and the PivotTable in two steps.

Answer 2

You can use the range directly without having to construct the range address

Dim ws As Worksheet, pc As PivotCache, pt As PivotTable
Dim UltimaFila As Long, rng As Range

Set ws = ActiveSheet
ws.Name = "RReal+RCEmin"

UltimaFila = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row
Set rng = ws.Range("A1:I" & UltimaFila)

Set pc = ws.Parent.PivotCaches.Create(SourceType:=xlDatabase, _
                                      SourceData:=rng)

Set pt = pc.CreatePivotTable(TableDestination:=ws.Range("O1"), _
                             TableName:="D1")