Solved

Adjust Page Breaks Automatically With a Pivot Refresh

Posted on 2011-03-15
5
281 Views
Last Modified: 2012-05-11
In Excel 2007, I have a pivot and a chart a few rows underneath it.  Whenever the pivot refreshes, and new rows are added, the pivot overlaps the chart.  How do I get it to just insert rows and move the chart down when the pivot adds new rows?  I don't want to keep having to update the page breaks.  I'm having the same problem when a new column is added to the pivot.  In the file, the cell A1 is merged across to the end of the pivot going across. When a new column is added to the pivot, the merged cells do not extend all the way to the new column. Can this be done automatically?
0
Comment
Question by:error_prone
  • 3
5 Comments
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 35147274
can you give us a exaple file to work with..

Kind regards

Eric
0
 
LVL 6

Accepted Solution

by:
Eric Zwiekhorst earned 500 total points
ID: 35147344
You could use the
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
this is triggered everytime a pivot table is updated.

Now you can detect just how many row are filled by the pivot and move the chart accordingly...


Kind regards


Eric
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 35148230
You can set page breaks that adjust automatically.  See this link:
            http://office.microsoft.com/en-us/excel-help/pivottable-reports-101-HA001034632.aspx
                        (Go to step 4 and expand the link)
0
 

Author Closing Comment

by:error_prone
ID: 35148718
Thanks
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 35154017
Dear Error prone,

I am glad I could help.
I like to point to a solution rather than give the entire code.
For me it was always better to make something myself with a little help then just copy paste the entire solution...

So thanks for the points and the grade

Kind regards


Eric
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now