Pivot Table: Optional Page Field
 

I created a VBA function to generate a number of pivot tables. For
each pivot table required, I supply 12 arguments used by the function.
For some pivot tables, however, I do not need a page field. How do I
make this pagefield optional? I give the code below. 'pfd' is the
pagefield which I want to make optional.

Thank you!
================================================== =============
Public Function CreatePvt(snm, ptn, rfd, cfd, dfd, fun, cal, nft, cap,
pos, stl, pfd)

Set pt = _
PC.CreatePivotTable(TableDestination:=ActiveSheet. Cells(dr + 5, 1),
TableName:=ptn)

'-------------add row column and page fields
pt.AddFields RowFields:=rfd, ColumnFields:=Array(cfd), PageFields:=pfd

'-------------add data fields
With pt.PivotFields(dfd)
.Orientation = xlDataField
.Function = fun
.Caption = cap
.Calculation = cal
.Position = pos
.NumberFormat = nft
End With

Set pt = Nothing

End Function

Pivot Table: Optional Page Field
 

Works like a charm, Jim.
Thank you so much. Again!


Jim Thomlinson wrote:
Just throw the term ooptional in front of the variable something like this...

Public Function CreatePvt(snm, ptn, rfd, cfd, dfd, fun, _
cal, nft, cap, pos, stl, Optional pfd)

Set pt = _
PC.CreatePivotTable(TableDestination:=ActiveSheet. Cells(dr + 5, 1),
TableName:=ptn)

'-------------add row column and page fields
if pfd = "" then
pt.AddFields RowFields:=rfd, ColumnFields:=Array(cfd)
else
pt.AddFields RowFields:=rfd, ColumnFields:=Array(cfd), PageFields:=pfd
end if

'-------------add data fields
With pt.PivotFields(dfd)
.Orientation = xlDataField
.Function = fun
.Caption = cap
.Calculation = cal
.Position = pos
.NumberFormat = nft
End With

Set pt = Nothing

End Function

--
HTH...

Jim Thomlinson


" wrote:

I created a VBA function to generate a number of pivot tables. For
each pivot table required, I supply 12 arguments used by the function.
For some pivot tables, however, I do not need a page field. How do I
make this pagefield optional? I give the code below. 'pfd' is the
pagefield which I want to make optional.

Thank you!
================================================== =============
Public Function CreatePvt(snm, ptn, rfd, cfd, dfd, fun, cal, nft, cap,
pos, stl, pfd)

Set pt = _
PC.CreatePivotTable(TableDestination:=ActiveSheet. Cells(dr + 5, 1),
TableName:=ptn)

'-------------add row column and page fields
pt.AddFields RowFields:=rfd, ColumnFields:=Array(cfd), PageFields:=pfd

'-------------add data fields
With pt.PivotFields(dfd)
.Orientation = xlDataField
.Function = fun
.Caption = cap
.Calculation = cal
.Position = pos
.NumberFormat = nft
End With

Set pt = Nothing

End Function