Auto Row Adjustment

    Dear All


    I have two pivot tables in one worksheet range from A5 to D15 and A17 to D25.


    Each time I update the data, I will goto "Refresh Data" in pivot data. If there is an additional product being key-in, the pivot table would extend from A5 to D18. That meant it would overlap the data in A17 to D25. Therefore, I would like to know is that any VBA code that can be used to ensure that there must be at least 2 empty rows being the above pivot table.

    I would like to rectify the last sentence. It should be "Therefore, I would like to know is that any VBA code that can be used to ensure that there must be at least 2 empty rows between the pivot table"

    From your description I assume (but can't tell for sure) that you are using VBA to create the pivot tables and place them on the worksheet. If this is the case, I assume that you are telling the second pivot table where (which cell) to start.


    If all of these assumptions are correct, then you should be able to use a NAMED DYNAMIC RANGE in the top pivot table to determine how far down the sheet to start the second pivot table.


    The attached workbook has nothing to do with your specific question, but it includes a named variable (INSERT > NAMES > DEFINE ... to view the names used and their addresses) that extracts data from a pivot table that changes length. You might find this useful. Also check Dave's Excel Tips on Dynamic Ranges from the EXCEL tab on the OzGrid Home page.

    Thank thomach


    Both pivot tables are not created by VBA. Would appreciate if you can give me some idea on how to auto insert 2 lines of empty rows in order to avoid overlapping of pivot table.

    why can't you move the source data to separate worksheets?


    it would save all this hassle! :)


    That may sound a bit flippant, but it is probably the best and least time-consuming solution.

    WillR


    In my working file, I have assigned each pivot table to each workhsheet.


    However, as for my report, I need to combine all the pivot tables into one page. This is the reason why I would like to know the alternative method which can save my time in copying each pivot table to one worksheet.

    Will,


    I think (learn, please correct this if I'm wrong) thet problem is that the pivot tables are on the same page and that if the top one grows, then it overrides the bottom one. (Rather than a data problem with the data sets overlapping.) I assume the pivot tables are adjacent for viewing convenience and that they should be close so that it's obviuos both are there.


    Without a VBA approach I don't see how to work this situation.


    learn,


    Perhaps the easiest thing to do is move the pivot tables to separate sheets. Or, if by some change their width never changes, to place them side by side rather than above and below each other. Also, are both tables subject to changing size. If not, place the static one on top (or to the left if you go to a side by side arrangement).

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login