pivottable title base filter

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Dynamic PivotTable title

Dynamic PivotTable title

resolvedResolved · Urgent Priority · Version 2010

Edited on Sat 7 Jun 2014, 16:52

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

Thu 19 Jun 2014: Automatically marked as resolved.


 

Excel tip:

##### displaying in Excel

When you get a series of hash symbols (####) appearing in some of your cells in a spreadsheet, this can make you think that you've make some kind of mistake.

This is a common misconception - what this actually means is that the cell is not wide enough to fully display the content of the cell.

All you need to do to see what is actually in the cell is to widen the column that the cell is in.

View all Excel hints and tips


Server loaded in 0.06 secs.