Very difficult printing routine

[Rees]

Hi all,

I have designed a spreadsheet that will place anywhere from 6 to 30 rows of information (starting at row 4 and down, Row 1 has the title and 2 and 3 are blank) over a varying number of sheets. i.e. could be as little as 2 sheets or as many as 50 sheets. The only fixed quantity is that if each sheet uses 21 rows, then all the sheets will use 21 rows. What I need to do is come up with a print routine that will print all the sheets in the workbook, but will keep all the information from any given sheet together; otherwise, it will put that sheet on the next page. In other words, it will not allow part of one sheet to print on 1 page and finish printing the rest on the next page. Also, I need only to get the title from 1 of the sheets and then need to skip over rows 1 to 3 for the rest of the sheets

For example,

If I have 35 sheets in the workbook, and each sheet uses 15 rows including a title and 2 blank rows at the top (columns don't matter). I need to grab the title of any one sheet, which is in A1, and then grab the rest of the sheets rows from row 4 down to 15, and print them all so that I have the title on the first page only and then each sheets group of information is on the same page. That means that on the average machine with the default fonts etc. I would only be able to get 3 sheets of information on the first page, but would be able to get 4 sheets of information on all the following pages, in this example, as each sheet is using 12 rows, and I am leaving the top 4 rows of the first page for the title and some blank rows. On my system here I can get 52 rows to print on one page.

I have created some code which kind of works, but needs to be set up on every individual machine to allow for the variance in printer drivers (I think)…I can get it to work on one computer, but then it is off by, say 1 row on another computer….or worse

Does anyone have any idea how to achieve something like this? I can post my code if you like, but it may just serve to be more confusing, as I couldn’t seem to figure anyway to do it with any built in excel properties like PageBreak or VpageBreak…

Maybe this is a simple thing...but I don't thinks so.

Anyways, any help is greatly appreciated.

Thanks,

Rees

 

[parry]

Hi Rees. Well you got me confused but I guess thats not very hard.

Perhaps something like this where you set a range for a print area (A4:F15 in this case) and place a title row of row 1.

Sub PrintTest()
Dim Rng As Range

Set Rng = [a4:f15]

With ActiveSheet.PageSetup
.PrintArea = Rng.Address
.PrintTitleRows = "$1:$1"
End With
Rng.PrintPreview

End Sub