Automatic Adjustment of Page Breaks Dynamically

[BeachRock]

Hello and thank you for any help you can offer. I'm trying to do two things that I need help with.

The range below in the Print_PDF_Player Macro, $B$1:$L$150, is actually the largest range that will contain any data to be printed and includes B1:L6 that I want to just be repeated at the top of each page.

I would like to make it so the range (B7:L150) is looked at dynamically, finding the last row containing any real data and setting the page breaks for 8.5 x 11 with margins set as 1/2 inch all the way around and with the previously mentioned range of cells repeated at the top of each page; B1:L6.

I found code for finding the last row with data but have no idea how I can integrate it into the Print_PDF_Player macro.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thanks!

[ARGK]

Hi Tony
Try this code:

Please Login or Register  to view this content.

I haven't tested it but it should work .
Good luck.
Tony

[OllieB]

Tony,

The combined routine would look like

Please Login or Register  to view this content.

You can use page set-up to further refine the layout of the PDF document created

[OllieB]

@AGRK,

perhaps it is better to use

Please Login or Register  to view this content.

to determine the last row as opposed to

Please Login or Register  to view this content.

as we do not know how the data is populated in the worksheet - the entries in column B may not represent the last line used in the range B to L

[BeachRock]

Ollie and ARGK, thank you both for your suggestions. I'll test them out this evening and let you know which I ended up choosing or if any errors happen. I think that either will work in this situation just as well considering that there will always be data present in column B if there is any data at all in any of the other columns for each row. However, very good to know to use .UsedRange.Rows.Count in situations where column B might not always have data when any of the other column could. Thanks for that, Ollie. :-)

[BeachRock]

ARGK and Ollie,
Sorry it has taken so long to get back to you. I've been in a VMware Administration training class all this week and been slammed with studying.

ARGK, I tried your code and it errored on the range line, which is what I was also having a problem with when I tried to add the code to find the last row.

Please Login or Register  to view this content.

However, I could see what was missing was $L$150. Replacing that into the line above fixed that problem and it now works but only prints page 1 and not page two which has about a half a page of data. That looks to be coming from "From:=1, To:=1" within the export to PDF function. The total possible number of pages could be as many as four and as few as one.

Please Login or Register  to view this content.

Removing it causes the export to take about a minute since it now includes 2495 pages.... But I don't see any way to allow for a variable number of pages with this per Microsoft: http://msdn.microsoft.com/en-us/libr.../ff198122.aspx


Ollie, I also tried your code and it works but since it is also taking into account the possibility of there being data in any of the other columns (C through L) the resulting PDF includes one full extra blank page at the end. It seems to be picking up a number from a formula I have in L151 (pulls the number of pool players in the league from a roster sheet) that has to stay where it is due to conditional formating using it that affects B7:B150 and D7:L150. I would have a ton of things to fix if I move it. Is there any way to amend your code to only look at the range of B7:L150 for the last row of data?

Rather than setting the repeating top rows within the macro I set them in Page Setup.

Thanks again to both of you.

[OllieB]

Try

Please Login or Register  to view this content.

[BeachRock]

Hi Ollie,

The updated code only ends up printing one page and only row 7 itself prints other than the repeated top rows. I've uploaded a copy of what I'm working with so you can see. On the "Reports" sheet you'll find a button to produce the Player Report.

[BeachRock]

Ollie,

I was wondering if you think it might be possible to manipulate the "To:=1" in the ExportAsFixedFormat code below so that "To:=" is able to change based on a cell reference on a sheet some how in order to adjust the number of pages that need to be printed. This could give it a dynamic ability that would eliminate the need for the code to be directly manipulated.

Please Login or Register  to view this content.

[OllieB]

Hi Beackrock,

For some reason the solution I provided earlier could not adequately determine the last row used. This mat have something to do with the value 54 shown in L151. Anyway, I have changed the code to deal with this. Please try the attached version

[BeachRock]

Ollie, it works perfectly!

I added more teams and players to the test to make sure it would lap over to page 3 and it did exactly what I was hoping for it to be able to do. Your efforts are greatly appreciated and I'm sure others will find it as useful as I do.

Thank you!!