Invalid procedure or call argument - Pivot table macro

[Jdw5511]

Hello,

I keep getting the run-time error '5' each time that I attempt to run my pivot table macro. Here is the line of code that keeps producing the error:


ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Temporary!A1:BJ1" & LRow, Version:=xlPivotTableVersion15). _
CreatePivotTable TableDestination:="Results!R3C1", TableName:="PivotTableBmStats", DefaultVersion:=xlPivotTableVersion15

Any assistance that could be provided would be greatly appreciated!

Thanks,

Justin

 

[RoryA]

What's the value of LRow?
Is there already a sheet called Results? If so, does it already have a pivot table on it?

 

[Jdw5511]

What's the value of LRow?
Is there already a sheet called Results? If so, does it already have a pivot table on it?

Hi Rory,

Thank you for your response.

LRow is the last row variable I have defined. When the code runs to that point it inserts a results sheet that is blank with no pivot table in it.

 

[RoryA]

LRow is the last row variable I have defined

I know that - what I am asking is what is the actual value of the variable when the code runs?

 

[Jdw5511]

In this specific circumstance, there are two rows that make up the dataset.

Here is the full code up until the error:

    Dim LRow As Long
    LRow = Sheets("Formatted data").Range("A3").End(xlDown).Row - 1
    
'Copy and paste filtered data from Formatted data tab'
  
    Sheets("Formatted data").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
      
    Sheets.Add Before:=ActiveSheet
    ActiveSheet.Name = "Temporary"
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues
         
    LRow = Sheets("Temporary").Range("A2").End(xlDown).Row - 1
      
'Add temporary pivot table to obtain metrics for results tab'
       
    Sheets.Add.Name = "Results"
        
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Temporary!A1:BJ1" & LRow, Version:=xlPivotTableVersion15). _
        CreatePivotTable TableDestination:="'Results'!R3C1", TableName:="PivotTableBmStats" _
        , DefaultVersion:=xlPivotTableVersion15
    Sheets("Results").Select
    Cells(3, 1).Select

 

[RoryA]

That still doesn't answer my question, I'm afraid. What is the actual value of LRow when the error occurs? (not what you think it should be, but what it actually is)

You also have a typo in the code - the source range should be:

SourceData:= _
"Temporary!A1:BJ" & LRow

and not:

SourceData:= _
"Temporary!A1:BJ1" & LRow

which could be related to your problem.