Solved

Adjust Page Breaks Automatically With a Pivot Refresh

Posted on 2011-03-15
5
283 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

810 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