Solved

Adjust Page Breaks Automatically With a Pivot Refresh

Posted on 2011-03-15
5
286 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

615 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