About Us

[email protected]

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Pivot Table Page Field	Nate	Excel Discussion (Misc queries)	4	December 24th 07 03:11 PM
Page field - pivot table	yshridhar	Excel Discussion (Misc queries)	0	August 1st 07 04:48 AM
Pivot Table Page Field	Neily	Excel Discussion (Misc queries)	3	February 24th 05 01:23 PM
Pivot Table Page Field	Jimbola	Excel Discussion (Misc queries)	0	February 6th 05 09:13 PM
Pivot Table Page Field	Todd Huttenstine	Excel Programming	2	May 6th 04 04:24 PM

Menu

About Us