2

I have an Excel Workbook with a Sheet that contains some company template headers with a Pivot Table placed underneath.

This document is to be used as a template for pivot reports and the headers have to remain intact above the Pivot Table however when items are added to the Page Filters, the Pivot Table tries to expand upwards and ends up overwriting the header rows above.

I need the Pivot Table to expand down the page instead of up the page when Report Filters are added.

Excel Example Screenshot

Community
  • 1
  • 1
AeroX
  • 3,387
  • 2
  • 25
  • 39
  • 1
    I would suggest moving these headers out of the worksheet and putting them in the Print Headers. This will not only provide more workspace, but remove distraction. Not really answering your question, but could be an alternative to consider. – Robert Co Feb 03 '14 at 12:57
  • @RobertCo That's a good alternative for most cases, the only issue with headers is the 255 character limit. Also these reports will mostly be shared digitally so they can be edited by other users rather then printed – AeroX Feb 03 '14 at 13:02

2 Answers2

1

After looking to see if there are and settings that can be changed to make the pivot table expand down the page I looked into how the functionality could be scripted using VBA. Adding the below code to the Worksheet via the Visual Basic for Applications screen checks the location of the PivotTable on each update and then moves it up or down the page if Page Filters are added or removed from the report. Setting the StartLineLet & StartLineNum variables to the Cell Address that you want the top left corner of the pivot table or page items to remain at.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim StartLineLet As String
    Dim StartLineNum As Integer
    Dim DataAddr() As String
    Dim PageAddr() As String

    StartLineLet = "B"
    StartLineNum = 6
    DataAddr = Split(Target.TableRange1.Cells(1).Address, "$")
    PageAddr = Split(Target.TableRange2.Cells(1).Address, "$")

    If PageAddr(2) <> StartLineNum Then
        Target.Location = "$" & StartLineLet & "$" & (DataAddr(2) + (StartLineNum - PageAddr(2)))
    End If
End Sub
AeroX
  • 3,387
  • 2
  • 25
  • 39
0

You can also instruct the PivotTable (via PivotTable Options > Layout & Format) to display the report fields Down, Then Over by changing the Report filter fields per column, e.g. from zero to the value of three.

This will move the PivotTable with a limited amount of three rows down, and consequently start adding Report Fields to the right (in blocks of three rows)

Alain
  • 1