ExcelBanter - Pivot Table Creation Macro fails with Error #5 "Invalid procedure call or argument"

2007 (compatibility Mode 2003)

In theory, a macro created by the recorder should run without issue as long as the correct objects
are activated/selected no?

The macro recorder records exactly:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
"Pivot of Transactions!R1C1:R2458C4", VERSION:=xlPivotTableVersion10). _
CreatePivotTable TableDestination:="Pivot of Transactions!R1C6", TableName _
:="PivotTable3", DefaultVersion:=xlPivotTableVersion10

If I copy that very code into a Sub(), I get an error #5
"Invalid procedure call or argument"

I am missing something obvious.

Debra Dalgleish responded to a similar question below on Jan 5, 2007

Otto Moehrbach View profile
More options Jan 15, 7:21 pm

Newsgroups: microsoft.public.excel.programming
From: "Otto Moehrbach"
Date: Mon, 15 Jan 2007 18:21:14 -0500
Local: Mon, Jan 15 2007 7:21 pm
Subject: Pivot Table Code in a For loop
Reply to author | Forward | Print | Individual message | Show original | Report this message | Find
messages by this author
Excel XP & Win XP
I know very little about creating Pivot Tables and less about coding it.
I have about 20 sheets in a MASTER.xls file and I need to build/create a
Pivot Table on each sheet with data from that sheet. I want to do this with
a For loop and therein lies my problem.
The following piece of code is the first part of a recorded macro that
creates a Pivot Table in a single sheet, "BIO", using data from column A:E
of that sheet and however many rows it has (11 rows in this case). Looking
at this code, one can see that the sheet name, database range, workbook
name, and destination cell are all fixed. To place similar code in a For
loop I will need to have the sheet name (2 places) and the database range
(R1C1:R11C5 in this one sheet) to be variables, say "ws.name" and
"PVTdbRng". The workbook name and the J3 destination cell will remain
fixed.
I have modified code of this type many times before, preserving brackets,
exclamation points, etc, but this code (after modification as above) will
error out with "Invalid Procedure call or argument."
My question: Can anyone modify this code as needed and/or direct me to a
source where I can learn more about this? Thanks for your time. Otto

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"BIO!R1C1:R11C5").CreatePivotTable TableDestination:= _
"[MASTER.xls]BIO, !R3C10", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10

Debra Dalgleish View profile
More options Jan 15, 8:30 pm

Newsgroups: microsoft.public.excel.programming
From: Debra Dalgleish
Date: Mon, 15 Jan 2007 19:30:57 -0500
Local: Mon, Jan 15 2007 8:30 pm
Subject: Pivot Table Code in a For loop
Reply to author | Forward | Print | Individual message | Show original | Report this message | Find
messages by this author
Assuming the worksheets all have data starting in the top left corner,
you could use code similar to the following, substituting your field names:

'===================
Sub CreatePTs()
Dim wb As Workbook
Dim ws As Worksheet
Dim wsPT As Worksheet
Dim strName As String
Dim strNamePT As String

strName = "Pivot_"
strNamePT = "PT_"

Set wb = ActiveWorkbook

For Each ws In wb.Worksheets
If Left(ws.Name, 6) < strName Then
wb.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=ws.Range("A1").CurrentRegion).CreatePi votTable _
TableDestination:="", TableName:=strNamePT & ws.Name
Set wsPT = ActiveSheet
With wsPT
.Name = strName & Left(ws.Name, 25)
.PivotTableWizard TableDestination:=.Range("B4")
With .PivotTables(1)
.AddFields RowFields:="Year"
.PivotFields("Total").Orientation = xlDataField
End With
End With
End If
Next ws

End Sub
'=======================

--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html