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

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
'=======================

Otto Moehrbach wrote:
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
Contextures
http://www.contextures.com/tiptech.html

Thanks Debra. I'll give that a go in the morning. Otto
"Debra Dalgleish" wrote in message
...
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
'=======================

Otto Moehrbach wrote:
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
Contextures
http://www.contextures.com/tiptech.html