Excel (Beta) 365 for Mac: How to repeat headings at each change of pivot table first field?

Try  this:

1. Right-click the row or column label you want to repeat, and click Field Settings.
2. Click the Layout & Print tab, and check the Repeat item labels box.
3. Make sure Show item labels in tabular form is selected.this

Apologies Randy I bent a basic guideline, by failing to mention this is for Excel Mac.
At point 2, I get this:

No layout and print tab. Yes, in tabular form.
Sometimes such "missing" things are to be found elsewhere, but it has me beaten.
Regards
Anthony

p.s. that's why my question is couched in terms of perhaps inserting invisible FF FormFeed characters, also that FormFeed  will activate my "print repeating rows" as opposed to Pivot Table headings which in my table are hidden.

I'll go edit my op with both the above, and thanks for effectively bringing this to my attention.

See if this helps:

On the PivotTable Analyze tab, near the left end of the Ribbon, click the Options button and then Choose Options.

Another way: Right-click anywhere in the PivotTable and choose PivotTable Options from the pop-up menu.

Yes, now you mention it been through the layout tab hidden in options
Yes tried options.





As you can see I have (already) ticked print row labels et al on each page.
But none of these cause a new page, or even just repeated rows would do without a new page.  

Here is the context, desire to repeat heading rows upon each change of class:

At the mo I am thinking either a separate pivot table per class (so multiple separate copies of this table, or dynamic arrays with heading rows in between. Both of these break the dynamic feature associated with both pivot tables and dynamic arrays - unless I have wondered maybe if I can attach the header rows also as dynamic arrays so the whole caboodle shifts up and down with changes. But that is def another question for EE if I try that and need help.

Meanwhile I found this in another place (thanks SE), but not tried it yet:

If you like to use the terminal, you can do this:

printf "\x0c" | pbcopy

Select allOpen in new window

This pipes a single formfeed character to the pbcopy, which stores it in the system clipboard. You may then paste it into whatever app you need.

edit: That works, but copying ^L in to the beginning or end of a row doesn't take effect.
edit: we can of course manually insert page breaks, but it is a laborious task over several tables every time something changes.

I appreciate your tenacity!

Anthony

I don't see a setting for "Show item labels in tabular form". From what I have seen online, that's not available for Excel 2011 for Mac. Copying the table may be the best option. 

Thinking here... would you willing to have macros?  I presume this is for actual printing / pdf exporting?

You could have a macro which inserts a page break after every change of category - could be triggered by a "print me" button or opening the sheet or anything else.

Effectively would need to loop through all cells in the left hand column and if the cell is different to the one above it then insert a page break, i.e. something like this:

Sub PageBreaks()


Dim mySheet As Worksheet
Dim myRange As Range
Set mySheet = ActiveCell.Worksheet


' Set the range to include the pivot table
Set myRange = Range("A5:A50")


mySheet.ResetAllPageBreaks


For Each c In myRange
   
    ' identify when have reached end of pivot table
    If c.Value = "Grand Total" Then Exit For
   
    ' If the cell is blank then insert a blank line one above it
    If c.Value <> "" Then mySheet.HPageBreaks.Add Before:=c
       
Next


End Sub

Select allOpen in new window

Edit:
Could get the range from the pivottable if you want to be more dynamic using this to skip the first row

Set myRange = mySheet.PivotTables(1).TableRange1.Offset(1, 0).Cells.Columns(1)

Select allOpen in new window

or if you aren't having headings in the pivot table:

Set myRange = mySheet.PivotTables(1).TableRange1.Cells.Columns(1)

Select allOpen in new window

Randy it's not 2011 it's Office 365 ProPlus (now called as Microsoft 365 Apps for enterprise) set to Beta channel.
Mac.

Tabular is the only layout I ever use.

Hi Steve, I avoid VBA. But! Where I can save the code in a sheet and paste it in to the VBA window and run it, then remove it, I will use "black box" routines if there really is no choice as the manual effort is so mega.

The Class shows once, then blanks until the next class.

 

I guess maybe even being able to enter a routine and manually click each row to insert a page break would be much less laborious than navigating all the menus for each one

so that might be

run macro

click
(allow me to navigate)
insert page break,
pause, click
loop

Is that a one liner?

Sub PageBreak()
'
' PageBreak Macro
'
' Keyboard Shortcut: Ctrl+b
'
    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
    ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=ActiveCell
End Sub

on the alt-cmd B key

What's that about Vertical page break?

You can do the same for that if you want, that's for the width of page though... let me knock up quick example of what I thought you wanted?

Pivot Table page breaks.xlsm

no I just want the horizontal, and the shorter the code the better
I am realising that what I am really complaining about is the lack of a shortcut key to horizontal page break

ActiveSheet.HPageBreaks.Add Before:=Cells(ActiveCell.Row, 1)

on a key sequence?

Have you tried what I did?  It does what you want as far as I can see, few lines of code on there to keep in the sheet all the time?

no sorry, my dad was on the phone needing remote access support - at 91 I tend to just react immediately and fix whatever it is. He's 91 not me ;-)

back to this now

can I omit the dims and sets?

let me see:

Dim mySheet As Worksheet DEL
Dim myRange As Range DEL
Set mySheet = ActiveCell.Worksheet ' DEL
 Set the range to include the pivot table DEL
Set myRange = Range("A5:A50") mySheet.ResetAllPageBreaks For Each c In myRange         ' identify when have reached end of pivot table     No need to restrict to pivot table, in fact don't want to as it might not be one in future
 If c.Value = "Grand Total" there are no grand totals. (these are circuit race results)

Then Exit For         ' If the cell is blank then insert a blank line one above it  
We can't insert blank rows in a pivot table 

  If c.Value <> "" Let me select the cell

Then mySheet.HPageBreaks.Add Before:=c

clear page breaks first is a great idea

If I am honest, what I want is one line of code I can re-use, as it will never be saved as a macro

ok, maybe two or three..

Can I ask why you don't want to just put it in the sheet as a macro to do it for you?

think I broke VBA, nothing will run now

Oh dear!  If you want to post a simplified version or empty of real data version of your sheet I can put the code in for you to try

Can I ask why you don't want to just put it in the sheet as a macro to do it for you?

Sure, I don't save macros ever. I only have one other and that lives in a (standard grid) sheet for rare use when I drop it in to VBA.
I think what you have shown me is what I want is a keyboard shortcut to Horizontal page break.
If I used VBA much at all I wouldn't hesitate, but I don't. I'm not even sure exactly what you mean with " just put it in the sheet" as I thought they lived in the VBA department, so I am wondering if "put it in the sheet" means a button and I def don't want that as there are potentially hundreds of these over time. Tail would be wagging the dog. Just guessing a bit though.

Oh dear!  If you want to post a simplified version or empty of real data version of your sheet I can put the code in for you to try

yeah sorry about that; I'll see what I can produce, just a few ticks.

EE-TABLE-BREAKS.xlsx

It's a Table instead of a pivot table, which in real live it maybe, it could also be a dynamic range.
I suppose if we are going to make it a real routine, how does it navigate down to the next class?From Class 1 can it do like "Ctrl-D" to drop to the next class, insert page break, same agin to class three etc? And so at the begining ask me how many of these I want? Since that changes and when you hit the last class there is nothing much until row one million - and you may not be in a table of any sort.

ah, forgot, this is in fact two pivot tables "glued" together, with all field rows hidden and spare columns between them also hidden, so designed to look like one table - trying to "compete" with dynamic arrays, which also have these repeating headings hard wired. 

if only I could include a FF on each class change row, job done; or embedded with the data

MS claims that vbNewline is somewhat universal

In may not be universal any more, but back in the good old days of
line printers the page break character was Chr(12). That is the ASCII
"Form Feed (FF)" character, and the VBA constant is vbFormFeed.

Is that useful?

I don't suppose we can embed a vbFormFeed.. in a cell or with a class number

If you have a procedure for doing this manually, you might try recording it as a macro. That will generate VB code for you. I suggest doing that with a copy of the spreadsheet since it may produce undesirable effects. If nothing else, the generated code may give you a start on some code that does what you want.
If all that goes well, you can assign the macro to a button and/or a key sequence.

Hi Randy, that's what generated my code above - the recorder, though right now nothing works, I think I have disabled VBA somehow. However, Steve seems determined to create one click gizmo, so we will see what magic he comes up with.
Meanwhile I have been scouring the net for ways to embed formfeeds and found lots but nothing that works in Excel. I THINK Excel is in XML (as in the xlsx) and I thought I found does the trick as &#13; (from here https://www.dvteclipse.com/documentation/svlinter/How_to_use_special_characters_in_XML.3F.html) but while I thought it did initially, it didn't.   13 is in fact a carriage return, but should do the same as FF.

Form feed is not an option, that is talking to a printer directly.  We have a way of putting in your form feeds needed in the sheet, and a way of identifying where they go and have written you code that puts them in automatically that is just a few lines.  I'll add it to your sheet and show you in a while.

• An XLSX file is a Microsoft Excel Open XML Format Spreadsheet file.

ok, I await with interest.

If nothing is working, you may have disabled macros.

Enable macros when the Message Bar appears

When you open a file that has macros, the yellow message bar appears with a shield icon and the Enable Content button. If you know the macro, or macros, are from a reliable source, use the following instructions:

• On the Message Bar, click Enable Content.
  The file opens and is a trusted document.

Wasn't that. However, I have equally mysteriously solved it. I suspect it was one of the stuck in a loop or "in edit mode" types of "error". 

If VB window was open, it probably was in debug mode.

like breakpoint halt or some such?
I did close the VBA window. 

Yes, typically it would warn you that closing the window would reset the code or something along those lines. Essentially VB had stopped so you could see the error or try to debug.

There weren't any warnings. Next guess?
Maybe this is a clue: I was messing with key sequences. Trying to change it from Alt-Cmd-B to Shift-Ctrl-D and it was not complying.. that's just come back to me; possibly I had unwittingly fired off some other shortcut , not VBA, just something else.

Gosh look at this list of apps that use shift ctrl d and I have several of them on this machine. Granted none of those running were in focus.  https://defkey.com/what-means/ctrl-shift-d

Shift-Ctrl-D keys probably fired off a macro. Closing down the VB window must have cleared it. 

there was only my one line macro. there are no others anywhere.

You could always try repeating the Shift-CTRL-D to see if your one line macro causes issues. At this point you know how to resolve that issue.

I did, it works fine. Now real advanced attached to a button.

Anthony, you might want to consider getting some macros you like using and putting them in a "macros" sheet as they can be available then from any workbook you open.  You can assign those to keys then or you can add macro buttons to the ribbon at the top.  I was going to write you out how to do that but had a quick google and this guy has already done a step-by-step here, not exactly the way I would have done it but probably easier to follow:  https://trumpexcel.com/personal-macro-workbook/

Effectively then you have a "personal macros" workbook open all the time that you don't see except in VBA Editor. You put your macros in there and then they are accessible to all ones.  One I use all the time is a simple one for instance which adds a month to the date in the cell -- e.g. I copy last month's regular bills like DD's in by bank rec. spreadsheet paste them in and (in my case) Control Shift Q on the dates and they are ready for next month... if I'm being super keen I paste again and add 2 months onto them.  I then hit another button which sorts my bank info based on the date and whether it has appeared on statement yet which update "current balance" etc.

Anyway you can keep such little macros elsewhere and paste the code in as on-eoff to do things, keep them in the sheet you use them in or put them in central one but so useful!  Above ones have been used literally thousands of times.

I know it's not relevant to this question but just to show the simplicity of doing something that takes a lot of effort to update 40-50 rows manually to new dates for instance is just this

Sub UpdateMonth()
For Each c In Selection
    c.Value = DateAdd("m", 1, c.Value)
Next
End Sub

Select allOpen in new window

or to sort... made from macro recording many years ago, that sorts how I wanted it then goes to the next blank row with "end-down"..

Sub SortBank1
' SortBank Macro
' Macro recorded 24/06/2011 by SK

Application.EnableEvents = False
    Range("A4201:H5999").Select
    Selection.Sort Key1:=Range("H4201"), Order1:=xlDescending, Key2:=Range( _
        "A4201"), Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase _
        :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal
    Range("A4201").Select
    Selection.End(xlDown).Select
Application.EnableEvents = True
End Sub

Select allOpen in new window

If you find yourself having to click more than a few ribbon buttons or shortcut keys in the right order, or even worse giving it to someone else to not get wrong then macros, great!

Did you try the sheet in https://www.experts-exchange.com/questions/29214788/Excel-Beta-365-for-Mac-How-to-repeat-headings-at-each-change-of-pivot-table-first-field.html#a43282294                                   
 btw?

morning Steve, thanks for all this, it'll take me a little time to work through it. I am just trying out your code and it's like the macros are present, but don't run when I select run.. I imagine I need to have the cursor in the table, does it matter where? Whatever it is, it will be a very basic mistake of mine.

All this because we can't use a printer control code.

I was once a LOTUS 123 macros dev, long ago. You may have read my mind about having a personal macros sheet, if that means all my files can be macro free and I can apply them like tools. Mostly I try very hard to achieve everything without macros, whereas decades ago due to 8086/8 cpu slowness I had to use macros to hardwire many of the formulas.

Also I find the absence of keyboard commands (these days called shortcuts I think) slows me down, as is happening here because the page break command is buried in a menu, even though I have added it to the quick access icons atop the window.

Anthony

if I record a macro I can run it, but yours not so. I'll try pasting your code in place of a recorded macro

.. simply nothing happens at all when I select "run" having chosen the named macro

nope. nothing works. feel so stupid.

finally!! Opened an original file, so not a test file, pasted in your code and it worked instantly - yes I remember the joy and satisfaction of a working macro.

also created a personal file

Good, it is a very simple macro and if you can work out how to do something for a single cell by googling it or by recording a macro then you can use a simple loop like the for each c in range("a1:A1000") or whatever and run the same thing for every cell etc.

If you need some more help with it or I've got the wrong end of the stick say...

Always available for paid work if you want to commission something specific too of course!

Steve

Paid work, I wish!
This stuff is just personal education in the absence of other.

Thanks I'll see how I go.

Anthony