Excel 2007 : Print Macro for occupied cells only and multiple sheets...

Hello,

First I want to thank the forum for the many posts that helped me to achieve setting up the protect issues we were having with the conversion to 2007. It was tough finding the topics that were specific to the issue but I did get there because of the work by the people on this forum. A big thank you.

Now I have a situation where the print macro is shot. Used to be set up to print 3 specific sheets (2 copies of each). In 2003 it seemed to work fine. In 2007 it is not working well at all.

Here is what is happening first.

There are 3 sheets. Two of them are BOM sheets which means that the total lines involved will vary each time the sheet is used. Unfortunately it prints out everything in the sheet (including 4 pages of empty cells) each time.

I had found a topic that covered this but I can't find it again. There are just too many posts to try and go through. Now that I am on hour 2 trying to find this again I am at the point of starting this thread.

The second issue is that the boxes and such are skewed now. We will have the lines drawn in surrounding a set number of cells and when we print they are usually a lot bigger covering other cells. The same thing happens with Logos and such. Maybe this should be a different topic or I may be able to find it elsewhere. Thought I would mention it.

Any help or suggestions on this would be greatly appreciated!

Thank you,

Mike

Sorry about that. I was worried it wouldn't make too much sense.

Here is the issue (and I will copy the macro next).

We have a document with 8 sheets. The first 3 are input sheets. Information placed in these will then input into 4 other sheets.

In the end we have these 4 sheets that we want printed out (2 copies of each).

They are the BOM, Labor Cost Estimate, Cost Summary and a W.O. sheet.

In the W.O. sheet we have a print tab with the macro that will print 2 copies of each of the sheets. The issue is that the BOM and Labor Cost Estimate sheets will vary in length per each use. When we print them with the Print tab on the W.O. sheet we get 4 empty pages of each. We want the macro to be set to print only the page break areas that have cells with data in them.

Here is the macro:

Please Login or Register  to view this content.

I had come across some code in other discussions on this site where a way to print only the occupied cells on a sheet with a macro was put together. First, I cannot find that thread again. Second, this is a little different because we are dealing with multiple sheets.

I had made an attempt to put the code in but it just didn't work out. Here is what I had there:

Please Login or Register  to view this content.

A second issue is with the output of the documents. When I send them to PDF they show up nice and neat and fit into the PDF. All of the lines and boxes are in the same scale and everything looks as it does in the Excel document. However, when I send them to the printer the prints cut off the right side of the page and the boxes and such are scaled up which obscures other items in the document.

Thank you very much for your time on this. This site has helped me tremendously with the switch to 2007.

Best regards,

Mike

I have sent you a private message on this.

However, I wanted to reply on the site in case there is a contributor that may have a fix in mind.

I can't post the document with the sensative info removed. It corrupts the macros. I can't take the time to reset all of the values to "x" since with are dealing with thousands of parts broken up into sections divided by objects and other text throughout the document. I only have so much time allotted to come up with a fix for this.

The reason we have 2 sheets set to simply print and then 2 sheets that we want to adjust on a project by project has a simple answer.

The 2 sheets we want to just print have established ranges. They are simply summary documents only. The range of cells occupied will never change.

The other two sheets are for materials and labor. These two sheets will change on a day by day, project by project basis. There will be dozens of different people using this every single day for 2 or 3 or more different projects. Some projects may only fill in 1 sheet worth of material and labor. But then there will be others that may use 4 sheets or more.

Most projects will use only 1 to 2 sheets. How the macro is set now we will get 2 copies of the summary sheets (which is what we want), 2 copies of the BOM and Labor sheets (which is what we want) but then 2 copies each of up to 4 blank pages.

With hundreds of projects being run each week you can see how this will add up fast.

If it can't be done I will simply tell them that we will have to print out each sheet individually and set the print range on a per project basis. I am simply trying to keep it as simple for them as possible.

Thank you all ahead of time for your help.

Mike