Avatar of Anthony Mellor
Anthony Mellor
Flag for United Kingdom of Great Britain and Northern Ireland asked on

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

Repeat per new page is offered.What about repeat headers when the first pivot table field changes?

edit: by "headings" I actually mean "print repeating rows" because my table heads are hidden, hence wanting a FF. The field settings "Layout" tab is not available in my version of Excel.

Can we embed a FF formfeed into the data? How?

Anthony
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Anthony Mellor

8/22/2022 - Mon
Randy Downs

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
Anthony Mellor

ASKER
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
Anthony Mellor

ASKER
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Randy Downs

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.

Anthony Mellor

ASKER
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

Open 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



Randy Downs

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. 
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Steve Knight

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


Open 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)

Open in new window


or if you aren't having headings in the pivot table:
Set myRange = mySheet.PivotTables(1).TableRange1.Cells.Columns(1)

Open in new window


Anthony Mellor

ASKER
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.

Anthony Mellor

ASKER
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.

 
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Anthony Mellor

ASKER
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
Anthony Mellor

ASKER
so that might be

run macro

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

Is that a one liner?
Anthony Mellor

ASKER
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?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Steve Knight

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
Anthony Mellor

ASKER
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
Anthony Mellor

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

on a key sequence?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Steve Knight

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?
Anthony Mellor

ASKER
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
Anthony Mellor

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Anthony Mellor

ASKER
clear page breaks first is a great idea
Anthony Mellor

ASKER
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
Anthony Mellor

ASKER
ok, maybe two or three..
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Steve Knight

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

ASKER
think I broke VBA, nothing will run now
Steve Knight

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Anthony Mellor

ASKER
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.


Anthony Mellor

ASKER
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.
Anthony Mellor

ASKER
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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Anthony Mellor

ASKER
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. 
Anthony Mellor

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

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Randy Downs

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.
Anthony Mellor

ASKER
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.

Steve Knight

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.

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Anthony Mellor

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

ASKER
ok, I await with interest.
Randy Downs

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Anthony Mellor

ASKER
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". 
Randy Downs

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

ASKER
like breakpoint halt or some such?
I did close the VBA window. 
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Randy Downs

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.
Anthony Mellor

ASKER
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
Randy Downs

Shift-Ctrl-D keys probably fired off a macro. Closing down the VB window must have cleared it. 
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Anthony Mellor

ASKER
there was only my one line macro. there are no others anywhere.
ASKER CERTIFIED SOLUTION
Steve Knight

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Randy Downs

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.
Anthony Mellor

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

Sub Pagebreak()
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
End Sub

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Steve Knight

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

Open 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

Open 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!


Steve Knight

Anthony Mellor

ASKER
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


⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Anthony Mellor

ASKER
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
Anthony Mellor

ASKER
nope. nothing works. feel so stupid.
Anthony Mellor

ASKER
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


All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Steve Knight

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
Anthony Mellor

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

Thanks I'll see how I go.

Anthony