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