The error I get is error 5 "Invalid procedure call or argument". I have given up on creating the pivotcache first and now use the code the macro gives, where you make both in one line. But I still get the same error. Here is the crucial part of the code. I include the full function at the bottom for reference, but thought that might be a bit of a job to wade through:
Code:
wkb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
strPTRangeName, Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:=strTableDestination, TableName:=strPTName
I'm beginning to think there may be something wrong with the computer since this code looks exactly like what the Excel macro puts out and like what I see on forums on the net.
Versions:
Windows 7 Professional Service Pack 1
Microsoft Office Professional Plus 2013
Code:
' Creates a new pivot table on a new sheet
Private Function funCreatePivotTable1(wkb As Workbook, _
rngTableRange As Range, strTableTabName As String, _
strQueryID As String) As Boolean
On Error GoTo ErrHandler
Dim wksPT As Worksheet ' New sheet for pivot table
Dim strPTSheet As String ' Name of pivot table sheet
Dim strPTName As String ' Name for pivot table
Dim strPTRangeName As String ' Name of named range to add to Names collection
Dim strPTRangeRefersTo ' RefersTo for new named range
Dim strTableDestination As String ' RefersTo for pivot table's upper left corner
' Includes name of tab
' Create named range in workbook
strPTRangeName = DLookup("[PTRangeName]", strNames, _
"[QueryID] = " & strQueryID) ' Get name of range from Names query
strPTRangeRefersTo = "=" & funCreateRangeAddress _
(strTableTabName, rngTableRange.Address) ' Create the refers to for the named range
wkb.Names.Add Name:=strPTRangeName, _
RefersTo:=strPTRangeRefersTo ' Add the name to the Names collection
' Create destination sheet for pivot table
strPTSheet = DLookup("[PTTabName]", strNames, _
"[QueryID] =" & strQueryID) ' Get sheet's name from Names query
Set wksPT = wkb.Worksheets.Add ' Create the sheet
wksPT.Name = strPTSheet ' Name the sheet
strTableDestination = "'" & strPTSheet & "'!A3" ' Create the range for the destination
' Get the name for the pivot table from Names query
strPTName = DLookup("[PTName]", strNames, _
"[QueryID] =" & strQueryID)
' Create the pivot table. Standard code from Macro in
' Excel workbook, with variables added
' This one uses the name of a named range for the
' SourceData argument. I have also tried using the
' RefersTo itself. That didn't work either.
wkb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
strPTRangeName, Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:=strTableDestination, TableName:=strPTName
GoTo EndFunction
ErrHandler:
LogError Err.Number, Err.Description, Err.Source, "funCreatePivotTable1 in MenuFunctions Module)"
EndFunction:
End Function