Run-time error '5' when creating a new pivot table

[jgscanlon]

I'm getting a "Run-time error '5': Invalid procedure call or argument" when running my code (below). I cannot figure out what I'm doing wrong here. Does anyone have any suggestions?

    Dim APXPvtSrcData As String
    Dim APXPvtCache As PivotCache
    Dim StartPvt As String
    Dim APXDataRowCount As String
    Dim APXPvtTable As PivotTable
    Dim i As Integer
           
    'Set pivot data range
    Sheets("APX Data").Activate
    Call CountRows(RowCount)
    APXDataRowCount = RowCount + 2
    
    APXPvtSrcData = Sheets("APX Data").Name & "!" & Range("A7:K" & APXDataRowCount).Address(ReferenceStyle:=xlA1)
    
    'Set Pivot Table location
    StartPvt = Sheets("APX Summary").Range("A3").Address(ReferenceStyle:=xlA1)
    
    'Create Pivot Cashe from Source Data
    Set APXPvtCache = Workbooks(OutputFile).PivotCaches.Create(xlDatabase, APXPvtSrcData)
    
    'Create Pivot Table from Pivot Cache
    Set APXPvtTable = APXPvtCache.CreatePivotTable(StartPvt, "PivotTable1")

 

[Sgdva]

Pivot tables always give error when some sheet involved has spaces among, try to change them for "_"
IE:
Sheets("APX Data") for Sheets("APX_Data") -of course in your book as well-

 

[jgscanlon]

I like that suggestion, but I'm afraid it didn't work.

 

[tiedied61]

Let take this one piece at a time:

1) Your source must be a Range object, not a String
2) Your destination must be a Range object, not a String
3) Does your source range include the header row? It must, otherwise the CreatePivotCache will error
4) you already know the Sheet, so why the long syntax??
5) sheet names with spaces work just fine, but you need to surround them with single quotes when creating a cell/range reference string
6) unless you/your user has changed something, the default reference style is A1
7) Missing PivotCache parameter in PivotTables.Add() method

Part I
Your source must be a Range object, not a String ... Also, be sure the range includes the header row

Your Code...

    Dim APXPvtSrcData As String
    APXPvtSrcData = Sheets("APX Data").Name & "!" & Range("A7:K" & APXDataRowCount).Address(ReferenceStyle:=xlA1)

Correct and simplify...

    ' Define the source Range object
    Dim APXPvtSrcData As Excel.Range
    Set APXPvtSrcData = Sheets("APX Data").Range("A7:K" & APXDataRowCount)

Part II
This line should be fine, with the correction above to a Range object for the source

Your Code...

   'Create the Cache ... 
   Set APXPvtCache = Workbooks(OutputFile).PivotCaches.Create(SourceType:=xlDatabase, SourceData:=APXPvtSrcData )

Part III
Your destination must also be a Range object, not a String

Your Code...

    'Set Pivot Table location
    Dim StartPvt As String
    StartPvt = Sheets("APX Summary").Range("A3").Address(ReferenceStyle:=xlA1)

Correct and simplify...

    Dim StartPvt As Excel.Range
    Set StartPvt = Workbooks(OutputFile).WorkSheets("APX Summary").Range("A3")

Part IV
You are missing the PivotCache parameter

Your Code...

    'Create Pivot Table from Pivot Cache
    Set APXPvtTable = APXPvtCache.CreatePivotTable(StartPvt, "PivotTable1")

Correct and name your parameters (for clarity, and so the order does not matter)

   Set xlPivotTable = xlSheetDest.PivotTables.Add(PivotCache:=APXPvtCache , TableDestination:=APXPvtTable, TableName:="PivotTable1")

Make these fixes and you should be on your way.
Hope this helps,
Art

 

[tiedied61]

Correction on the very last block, you add a pivot table to a sheet and I forgot to replace my variable names with yours

Corrected code...

Set APXPvtTable= Workbooks(OutputFile).WorkSheets("APX Summary").PivotTables.Add(PivotCache:=APXPvtCache , TableDestination:=APXPvtTable, TableName:="PivotTable1")

Art

 

[jgscanlon]

WOW!

Thank you so very much for your in-depth analysis and explanation! With your recommended changes, this pivot table is now being created just where I want it.

Thank you!