Nil has attended:
No courses
Dynamic PivotTable title
I have pivottable with a filter selection that I would like to select ALL and I have inserted a page break after each filter Item.
Now , I would like to print each page with their filter selection as page title.
So, my Title on my printed page must be equal my filter...
How I can do it?
here is what I wrote which doesn't work, but if there is any other ways, I would like to hear about that.
Thank you.
RE: Dynamic PivotTable title
Hi Nil,
Thank you for the forum question.
I guess that you want to do this using Excel VBA.
You wrote:
"here is what I wrote which doesn't work, but if there is any other ways, I would like to hear about that."
but you didn't add the code or the code was not in the question when I received it.
Please forward me the code or send the Excel file to:
info@stl-training.co.uk
Please add my name in the subject.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Read more: https://www.stl-training.co.uk/post-34560-formula-shows-quarters.html #ixzz2wE8kbKnu
RE: Dynamic PivotTable title
Thank you so much for reply. It is very hard to explain, but I will try my best.
One my test file that I have attached to the email I have a sheet called Report. I would like when I click on print or save as pdf will print all pages as one file and each Center that I already set page break for; add the specific center name as its title:
For example if my filter includes 3 items; so between item1, item2, and item3 set break page and on item1 page the title or header or caption shows item1 and on item2 page the title shows item2 and the same thing for item3.
because in future we may add more items and I would like titles dynamically changes based on my specific row label or report filter.
Please let me know how I can do it, if there is any VBA exist for this or itโs as easy as just to click something.
Thank you very much,
RE: Dynamic PivotTable title
Hi Niloufar,
Sorry it has not been possible for me to find a dynamic way of doing what you want. I have tried to loop through the pivot fields to find the pivot field with the Orientation = xlRowField and the Position = 1, which you need to find to add the property LayoutPageBreak = True to get the page breaks.
Unfortunately you will need to right click the field and click field settings and tick the box "Insert page break after each item" under the "Layout & Print" tab each time you change the field you want to use for the page breaks.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Read more: https://www.stl-training.co.uk/post-34560-formula-shows-quarters.html #ixzz2wE8kbKnu
RE: Dynamic PivotTable title
Hi Jens,
last question, this will loop through my 1st column:
Sub testing()
Dim pt As PivotTable
Dim varFilter
Set pt = ActiveSheet.PivotTables(1)
varFilter = GetItemList(pt.ColumnFields(1))
MsgBox varFilter
End Sub
Function GetItemList(PF As PivotField) As String
Dim pi As PivotItem
Dim strOut As String
For Each pi In PF.VisibleItems
strOut = strOut & "," & pi.Caption
Next pi
GetItemList = Mid$(strOut, 2)
End Function
is there anyway to find the break row between my filters to insert each item into it?
RE: Dynamic PivotTable title
Hi Nil
Thanks for your reply. I did get very close but not quite, here is the code I used to access the items in the PivotTable row:
Sub testing()
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables("PivotTable1")
For Each pf In pt.PivotFields
LayoutPageBreak = True
Next pf
End Sub
This inserts a pagebreak after every single item, not just the first Row Item. It's very close though.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
