VBA: Print page range

  • I have an Excel workbook, I want to be able to print from one worksheet using a command button, but I also want to print a specific number of pages that will always range from page 1 to a number that is embedded in a cell on that sheet to print. The worksheet that is to be printed is already formatted so that each page will print correctly, but it has over 100 formatted pages, but may not always have data to print out on every page, so the range will always be different to print. I have knowledge in VBA and I know how to create a command button, but with this, the command goes to the worksheet to print but then returns to the worksheet the command button is on and prints that page. I thank you in advance for any/all help given. :)


    Kenneth

  • Here are two examples that maybe you can work with. The first example code is just code.


    The second example code goes with the attached file.


    Hopefully you can find something in both of these to use for your purposes.



    Another Suggestion:

  • Ok, I don't think it really needs to be that complex, I have a more simple VBA, but it's not working to print the page i want it to. Instead it goes back to the page the command button is on and prints that page. i want to be able to click the command button and it go to the worksheet print pages listed and come back to the central page. this is a copy of the VBA Code I am using.


    Defined:

    RMAPRINT is the commandbutton name

    RMA ORDER is the worksheet that finds the total pages to print

    RMA ORDERS is the worksheet that i need printed

    CONFIGURE is the worksheet that that is the central page




    CODE

    Private Sub RMAPRINT_Click()

    If (Worksheets("RMA ORDER").Range("B1") = 0) Then

    GoTo NOPRINT

    End If



    If (Worksheets("RMA ORDER").Range("B1") = 0) Then

    GoTo NOPRINT

    End If

    Worksheets("RMA ORDERS").Activate

    MsgBox ("REPORT CONSIST OF ONLY: " & Chr(13) & " " & Worksheets("RMA ORDER").Range("B1") & " Pages ")

    PrintOut (1), Worksheets("RMA ORDER").Range("B1")

    Worksheets("RMA ORDERS").Activate

    Worksheets("RMA ORDERS").Range("A1").Select

    SendKeys "{ESC}", True


    End


    NOPRINT:

    MsgBox (" NO PAGES TO PRINT" & Chr(13) & Chr(13) & " CLICK OK TO CONTINUE")

    Worksheets("RMA ORDERS").Activate

    Worksheets("CONFIGURE").Range("CQ128").Select

    SendKeys "{ESC}", True

    End


    End Sub


    Can you give some advice on why this won't work.

    Thank you in advance,

    Kenneth

  • Your initial description indicated you wanted to print "several pages". Based on your second description and the sample code you provided,

    I have made a few edits to the code.


    See if this does what you are seeking :


  • I have attached a partial workbook, there are more worksheets, but if i can get it to print one, i can conform it to print all. i just wanted a central location to print all worksheets individually without printing all pages from each one, because not all pages need to be printed. I had to compress the file so that it would accept it to upload. Thank you in advance.


    Kenneth

  • Most of your workbook formulas are referring to the CUST ORDERS sheet. BUT ... the formulas for the most part are referencing

    cells in Col A and all of those cells are blank. So .. the first problem is I do not understand how the CUSTOM ORDERS sheet is to

    populate data based on empty cells.


    Getting away from that ...


    The orders that you need to print I presume are based on data located in the CUST ORDERS sheet ... is that correct ?


    Each order is then based on the CUSTOMER name which is located in COL I ... is that correct ?


    If you are not opposed to using VBA macros (which it does not appear you are because you already have macros in the workbook),

    you can separate all the orders by the CUSTOMER name. This would be the first step. Their order would be copied to a PRINT page.

    Then the macro will print that page, then clear that page in preparation for the next customer. The process repeats until all customer

    orders have been printed.


    I also was able to reduce the overall size of your workbook by deleting thousands of rows of "ghost data". That is data that is written

    to cells below those cells which you can see are not blank. BUT, the "ghost data" doesn't show up for you to see. Confusing .. yes I know but

    sometimes EXCEL will do that and each time it does the size of your workbook increase substantially. More often than not I've learned this

    "ghost data" is created when there are errors in a workbook ... each time you run through the code to correct errors more "ghost data" is

    written until all the errors have been eliminated. Then the "ghost data" creation stops.

  • Yeah, the workbook is huge, but it works, I deleted enough just to get it to you. All i am wanting to do is print from the CONFIGURE worksheet and it print the same way as it does if i were on the CUSTOM ORDERS worksheet. Click on the command button in both sheets to identify what is and what isn't happening.

    Thank you in advance,

    Kenneth

  • "All i am wanting to do is print from the CONFIGURE worksheet and it print the same way as it does if i were on the CUSTOM ORDERS worksheet."


    The attached is what prints now ....


    Please answer these questions :


    The orders that you need to print I presume are based on data located in the CUST ORDERS sheet ... is that correct ?


    Each order is then based on the CUSTOMER name which is located in COL I ... is that correct ?

  • Ok, I feel like it is unclear of what I am trying to get accomplished, I just need the 23 pages on the CUST ORDERS worksheet to print out from the command button on the CONFIGURE worksheet, but I need it to print only the pages that have data, which are only 23 of the possible 150 on the CUST ORDERS worksheet. I can print it off from the worksheet itself, but I’d rather just do it from the CONFIGURE worksheet. In the end there will be about 50 different worksheet that I may have to print to, but not all 50 all the time.I figured out that if I can pick and choose what to printout from one worksheet, it would be quicker and easier. I only have one worksheet in this workbook so that I will be the model and I can structure all other from it. Thanks again in advance.

    Kenneth

  • I would like to reach out to anyone that might have a solution to this problem. I have a workbook that I am trying to print however many pages that are counted from a specified cell from another worksheet, by using a command button from one worksheet to print the page range of another worksheet. The pages will always start on page 1 but could end on page 150. The specified cell is already defined for the last page number, but the VBA code I am using only prints the page of the command button. I need it to print the worksheet with all the pre-defined pages. Thank you in advance.

    Kenneth

  • I would go a total different route which would make your workbook size a lot smaller also.

    Have one sheet as your invoice/order sheet as you have now as a page in one sheet.

    Have a sheet with the data, like a database, and copy all the info for each row over with an array, print the sheet and go to the next one.

    Should be faster also.

  • I believe you are making this little more difficult that it should be. I do not need the help to sort anything, this is already done when I download the linked file that populates the STARTUP worksheet. I just need the VBA - command button to print the CUSTOM ORDERS worksheet. It is already formatted to fit on a 8 1/2 x 11 sheet of paper and the way I need the worksheet to be printed out. There are 150 pages but all may/may not be populated. The number of sheets to print will come from cell B1 of CUST ORDERS. I can use the command button on the CUSTOM ORDERS worksheet, but because I have about 50 other worksheets like this one, I want all sheets to be printed from one central location. so that I can pick and choose which worksheet I want to print out. I tried to use the same code as on the worksheet, but it just came back and printed the CONFIGURE worksheet. I don't mean to sound ungrateful for your help, but I do need the right solution to my problem. Please, go to the CUST ORDERS and click on the PRINT ALL command button (select to print to file or PDF if you don't want the 23 pages to print on paper), and then go to the CONFIGURE worksheet, click on the CUSTOM command button, it will print the CONFIGURE page only. If at all possible, i would also like a CANCEL to show up in case i don't want to print off the worksheet. My printer is defaulted to a 2 sided print, so if I could possibly get a code to change that setting just for this excel workbook, that would be a great help as well. As always, i thank you in advance for the solution.


    Kenneth

  • I deleted the downloaded attachment because it is unwieldy. If you don't want to take the time to make a representative workbook without all the shenanigans we don't need, I don't have the time to look into it. Apparently I am not the only one that thinks that way. Read Post #6 again.

    All we need is a workbook without any formats, formulae etc that bogs down the normal workings.

  • I have reattached a clean copy of the workbook. Please understand that I am not trying to restructure the worksheet in any way, i am just trying to print it from a central location.

    The CONFIGURE sheet is my central location and i am trying to print off the CUSTOMERS ORDERS worksheet, but not in it's entirety, just those pages that are defined in cell B! of CUST ORDERS. The VBA in the command button (CUSTOM) on the CONFIGURE worksheet doesn't work correctly, but the command button (PRINT ALL) on the CUSTOMER ORDERS worksheet works fine (pretty much the same code). As I said in my previous post, I also would like the command button to print the CUSTOMER ORDERS worksheet (# of pages 1-??), a CANCEL execution, and if possible, to change the default to "one side only" from the printer that is set up as two sided printing. Thank you in advance.


    Kenneth

  • I am sorry. You'll have to wait for someone that wants to work with an attachment like that.

    Just for your information, a sheet with with formulas in 10,000 rows x 15 columns is going to give you problems. Even if you say you don't want to change anything in the workbook. And that is after you brought it down to a so called manageable level.

    Good luck and a happy easter though.

  • The way your workbook is set up, you might be able to go on an extended holiday while this runs.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!