Protected Sheet / Page Breaks / PDF

  • I need help.

    I am not sure this can be done.

    On my password protected worksheet I have a toggle button which allows me to hide / un hide rows. Everything works perfectly.

    I have another button which generates a PDF from the same worksheet. Everything works perfectly.

    The same worksheet has many page breaks. One per section. When all my rows are not hidden, everything works perfectly. The PDF generated is exactly what I need. The layout is perfect and well balanced.

    What I hate is when I hide rows and generate the PDF, I still get the same page breaks. I would like to find a way to disable / ignore the page breaks when I convert my worksheet to a PDF via a macro so I have everything on 1-3 pages instead of 10-11 pages. I don't want to remove the page breaks. I just don't want them to be considered when the PDF is generated. The goal is to reduce the number of pages (after activating the toggle button to hide many rows) in this precise scenario.

    Is there a solution to this?

    Thanks x 1,000,000.

  • If nothing works, this will get you out of a bind if you take the time to fill in the pagebreaks just once.

  • I have two macro I wish to work with.

    The first one : Button clicked will generate PDF.

  • The second one : generate PDF and add to Outlook e-mail.

  • I have about 15 identical worksheets. They all have about 10 page breaks except a summary page.

    I have a code in each sheet linked to a toggle button which will hide lines.

    The PDF I will generate using macro 1 and macro 2 must exclude page breaks. Reason : my PDF would be too long for nothing as 3/4 of the worksheet is hidden. If it is possible I want to ignore the page breaks in the macro 1 and macro, This way I'll be able to reduce the size of the document from 10 pages to 1-2 page(s).

  • Oh yeah, I can't have the page breaks deleted each time the macro is ran where I would have to set them up again on each worksheet.... 8o

    At some point, there might be variances in page breaks from one worksheet to another...

  • Try this with a copy of your workbook.

    The PDF file will be saved in the same folder where your excel file has been saved.

  • Thank you.

    I copied the code in a new module.

    I ran the code. Got this error : Run-time error '9' : Subscript out of range. The following line is highlighted in yellow in debug.

    1. pbArr(i) = ActiveSheet.HPageBreaks(i).Location.Row
  • Hi jolivanes,

    There is no vertical page break.

    I get an error on :

    1. For i = 1 a - 1

    Here is the code.

    How can I prompt the user to save in the direct he / she prefers?