Const sDaysField As String = 'Days' 'Set reference to the first pivot table on the sheet 'This can be changed to reference a pivot table name 'Set pt = ActiveSheet.PivotTables('PivotTable1') Set pt = ActiveSheet.PivotTables(1) 'Set the names back to their default source name For Each pi In pt.PivotFields(sDaysField).PivotItems 'Bypasses the first and last items ' ' ' Then pi.Name = pi.SourceName End If Next pi 'Set the names to a custom number format For Each pi In pt.PivotFields(sDaysField).PivotItems If Left(pi.Name, 1) ' ' Then 'Change the 'm/d' format below to a custom number format. In fact, there is no layout / print settings tab in the field settings pane. In excel for mac that option is not present. It would print each item and subtotal for that item on a separate page. This is usually Days or the name 'of your date field. In Excel for windows there was an option in Pivot Table field settings to insert a page break after each item. 'Source: Dim pt As PivotTable Dim pi As PivotItem 'IMPORTANT: Change the following to the name of the 'grouped Days field. (54.2 KB) The Change Days Field Formatting Macro Sub ChangeDaysFieldFormatting 'Change the number formatting of the Days field 'for Grouped pivot table date field. Download the File Download the Excel file that contains the macro. Just remember that the item is NOT going to contain the year, since the item is not an actual date.
0 Comments
Leave a Reply. |