Excel Print Range Question

[jwpiii]

I posted this in the Office Forum, but I think it may be better suited to this forum. While I am not a programmer, and I am sure there are much better ways to complete this task, here is what I have done.

I need to print a report that may be 1 to 5 pages in length. The data for the report is to be displayed in a template that I have created in columns ek thru ez. I have copied the template five times so that it extends from ek2 to ez286. There aren't any empty rows between the pages. Page 1 ends on row 58, while page two starts on row 59.

I calculate the results for the report, and move them to the templates for the total length that is necessary. I have the number of pages stored in EI6 and based on the value of EI6, I have the last row of the desired print range stored in EI14. I even used concatenate to store the print range in EI16 (example of EI16: =concatenate("EK2:EZ",$EI$14) resulting in EK2:EZ229 if four pages in length)

Length Range
1 Page EK2:EZ58
2 Pages EK2:EZ115
3 Pages EK2:EZ172
4 Pages EK2:EZ229
5 Pages EK2:EZ286

The report will always be portrait, 1 page wide, and printed on letter size paper in black and white (I have used Color for shading). Currently, to fit on the page properly, each page must be scaled to 97% of its normal size.

How do I establish the print range based on the result contained in either EI6 (number of pages)or EI14 (last row of print range)? Given the scaling need, do I create a loop from 1 to (x pages) and print each page individually? Do you establish the print range and set page breaks at rows 58,115,172 and so on? Do I attempt to resize the template so that at normal size, there would be no scaling necessary? So many questions, and yet so little knowledge. HELP!!

Also, this is my first attempt at creating an Excel Macro using VBA (I would like my first time to be a pleasant experience)

Thanks in advance for your help.

 

[ssVBAdev]

Is there are reason why you have five templates for each page? I mean, if it is a heading say that is the same from page to page you can select a "Rows to repeat at top" range from the "sheet" tab in the "Page Setup" dialog box. Then you can select the "print area" (on the same tab) and Excel should print the pages you require. You say you are putting together a macro. It would help If I could see the macro and see better what you are trying to do. Can you post your code?

 

[Loomah]

Hi jwpiii
"So many questions, and yet so little knowledge" That's what the site's for!!

But before I go any further I have one question
Is the report length of 58 rows per page set - unchangable - or can the full thing break wherever it likes?

I would also like to make a quick suggestion to get you started. Try recording yourself doing the page setup stuff(TOOLS>MACROS>RECORD NEW MACRO) initially then we can work on changing bits of it to suit your specific needs.

You can get rid of the header & footer lines if you aren't using them and some other stuff to.

To start you off with the print area change whatever is recorded to this line

With ActiveSheet.PageSetup
.PrintArea = Range(Range("EI16").Text).Address
End With

This will set the print area to whatever you have in EI16 ie your concatenated range. A start!
;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[jwpiii]

Loomah

Thank you!! Thank you!!

I had already recorded the macro using the full print range.

I made the change you provided for the print area.

For testing purposes, I chose an option (in another area that generates the report and thus controls length) that would result in a 3 page report.

I ran the macro and magically, three pages of correct data print. It's amazing what 33 characters can do. At least the right 33 characters.

Thanks again, I will now set off to get myself in over my head again. I use the three hour rule. If I don't resolve it on my own in three hours, I ask for help. For me, there's no better way to learn than to first struggle before asking for assistance.