Automatically creating a properly sized spreadsheet

[Katharina123]

Hi Everyone,

I've been searching for this for over an hour already, so I thought I'd give posting the question on the forum a try. While, I did find many similar questions and answers, they always related to a slightly different scenario. Here is what I am challenged with:

Backround Info:
I have a "Master Spreadsheet", which I use to quote individual orders. Each time I quote a customer order, I start out with my "Master Spreadsheet". This spreadsheet includes the following tabs:

- Pricing Tab: References all products and associated prices available for sale within my company
- Customer Order Tab: This is where I select the products for the specific Customer order. The products are selected from a drop down menu and the associated prices are automatically generated by referencing the "Pricing Tab"
- Vendor Order Tab: This tab pulls together information from both of the above tabs. It automatically lists all of the products selected on the "Customer Order Tab" as well as related product codes from the "Pricing Tab". This tab
allows me to automatically create the Vendor Order without having to fill out a vendor order form separately.

Problem:
The Vendor Order tab is what I am having difficulties with. Right now I have the tab set up so that an extremely long order (lets say a 10 page order) will automatically be filled in. When it becomes very inconvenient is when I need to place a short order (only 1 page or so). Its very unpractical because the actual order information sits on the first page, while the totals etc. are on the very last page (page 10!). It looks really silly placing a short/small order using a 10 page document, where most pages are blank.

Solution:
I am looking for a solution that still automatically creates the Vendor's Order on the Vendor Order Tab pulling info from the other two tabs. However, it would be fantastic if the Vendor Order Form was of appropriate length and the totals showed up underneath the last product line, regardless whether that info is on page 1 or 10 or 100.

While searching for a solution, I've come across some suggestions, but really nothing that actually automates this process properly. I have other less Excel savy users of this spreadsheet, so I am really trying to stay away from manually editing the "Vendor Order Form" by deleting/adding liens. It would be great if the properly formatted Vendor Order could be created without any additional work, aside from creating the order in the "Customer Order Tab" of course...otherwise that would be magic.

Thanks so much for any help!

 

[SpillerBD]

Would it be practical to use Auto-Filter to hide the excessive blank rows before printing?
If so a macro could be applied to prepare document for printing. More specific suggestions may depend on more detail of how that sheet retrieves its data and the layout of its source. IOW, you might need to share your Workbook in some manner.

 

[Chrisdontm]

How about setting up a simple macro button that will hide all empty rows, but if you don't want a button, then you can use a shortcut combination instead.
One other option I used once was to have the next tab with only formulas that copied everything from the previous tab over. You could have different tabs for different sizes...
(Just thinking outside the box). : o )