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.
If you like to use the terminal, you can do this:edit: That works, but copying ^L in to the beginning or end of a row doesn't take effect.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.
printf "\x0c" | pbcopy
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
Set myRange = mySheet.PivotTables(1).TableRange1.Offset(1, 0).Cells.Columns(1)
Set myRange = mySheet.PivotTables(1).TableRange1.Cells.Columns(1)
Can I ask why you don't want to just put it in the sheet as a macro to do it for you?
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 tryyeah sorry about that; I'll see what I can produce, just a few ticks.
MS claims that vbNewline is somewhat universal
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 UpdateMonth() For Each c In Selection c.Value = DateAdd("m", 1, c.Value) Next End Sub
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!
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