Protected Sheet / Page Breaks / PDF

  • OK. I made the change. I get Compile error : Sub or Function not defined and highlight in blue ExportAsFixedFormat....


    At the same time, can I change the code to make allow the user to browse to the folder where he / she would like the document saved?


  • If you look at the code I supplied you'll see that there is a period (.) preceding ExportAsFixedFormat.

    Re: "At the same time, can I change the code to make allow the user to browse to the folder where he / she would like the document saved?"

    Yes you can but I am waiting for the

    "Oh I also would like to specify the file name"

  • Wops. Thanks. I added the missing "."


    As for the file name, can you tell me how to use the tab name as the file name and then ask the user where he / she would like to save the file?


    After running the code below, I get the following error :


    Run-time error 9 : subscript out of range --- line highlighed in yellow : ReDim pbArr(1 To a -1)

  • Does this work for you?

    Note: The file name HAS to be in Sheets("Sheet1").Range("L1"). Change the actual in the code. Just the name. No extension.

    Above has been changed after reading Post.


    If you want an InputBox for the file name instead of it being in cell L1 of Sheets("Sheet1"), this should do that.


  • 2 more suggestions for you. No worries about the page breaks with these.

    As suggested in Post #11 and probably closest to what you asked for in Post #13.


  • Good morning jolivane. Thanks again for helping out. Really appreciated.


    I am working with the following suggestion provided above. The macro doesn't provide any errors. :) ...However, the PDF generated is blank.


  • If we use Column A, as in the code, to get the print area, we'll have a blank sheet as in your case.

    Code
    1. .PageSetup.PrintArea = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).Resize(, 9).Address

    It is generally understood that you should either let us know these things or change them yourselves.

    You know what your workbook looks like, we're just guessing.

    So change the line to set the print area to

    Code
    1. .PageSetup.PrintArea = Range("B2:B" & Cells(Rows.Count, 1).End(xlUp).Row).Resize(, 5).Address

    Make sure you include the period when pasting this into the code.

    If there are any more errors, attach a copy of your workbook so we can all see what we've got to work with


    Don't quote whole posts. Just clutter we don't need.

    Refer to Post #'s or explain what is needed.

  • Thank you Jolivanes.


    Good news. PDF was created. The first row of my worksheet (which is row 2) appears in the PDF.

    I'll play with the values in post 29 to figure out how to make all rows (not only row 2) in worksheet appear in the PDF. :)