Page 1 of 2 1 2 LastLast
Results 1 to 10 of 19

Thread: A simple printing pages depending on cell value

  1. #1

    A simple printing pages depending on cell value



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I hope someone can help because this is driving me absolutely bonkers. This should be something so easy yet....

    I am trying to print a number of pages depending on a cell value which is a value of a formulae

    Sub PRINT_GENERIC()
    Code:
    Sheets("GENERIC").Select
    
    a = Range("Z1").Value
    ActiveWindow.SelectedSheets.PrintOut From:=1, To:=a, Copies:=1
    Sheets("MAINSHEET").Select
    
    
    End Sub



    The whole worksheet has page breaks in it to make 40 pages. Every time the above macro is run it will print 40 pages regardless of the value of (a).

    What am I doing wrong?

    Thanks
    Last edited by antonywalsh; 2012-05-08 at 03:56 PM.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,208
    Articles
    57
    Blog Entries
    14
    I'm curious if you can try this and let me know what happens...

    Code:
    Sub PrintMe()
    With Worksheets("Generic")
        MsgBox "I should be printing pages 1 to " & .Range("Z1").Value, vbOKOnly + vbInformation
        .PrintOut From:=1, To:=.Range("Z1").Value, Copies:=1
    End With
    Worksheets("MAINSHEET").Select
    End Sub
    The biggest question is what the messagebox says... how many sheets does it say vs how many do you get?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Ken

    Thank you for your reply.

    It comes back with the correct value whether its 1 page, 2 etc. but then will still print the full 40 pages.

    I don't know if this information will help but if I set my print to PDF its works fine and generates a PDF for the correct number of pages, if I set it to printer it prints 40 pages.

    All of the worksheets have been set up beforehand with the print area to equate to 40 pages.

    I don't think I have ever been so frustrated doing a spreadsheet, I have spent hours on this trying to figure it out.

    Regards





    Quote Originally Posted by Ken Puls View Post
    I'm curious if you can try this and let me know what happens...

    Code:
    Sub PrintMe()
    With Worksheets("Generic")
        MsgBox "I should be printing pages 1 to " & .Range("Z1").Value, vbOKOnly + vbInformation
        .PrintOut From:=1, To:=.Range("Z1").Value, Copies:=1
    End With
    Worksheets("MAINSHEET").Select
    End Sub
    The biggest question is what the messagebox says... how many sheets does it say vs how many do you get?

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,208
    Articles
    57
    Blog Entries
    14
    I don't suppose that there is any way that you could share the spreadsheet with us, is there? I'd be curious to have a look at the specific sheet to see if I can see anything. It doesn't make a lot of sense that it would work for PDF but not for printing.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Quote Originally Posted by Ken Puls View Post
    I don't suppose that there is any way that you could share the spreadsheet with us, is there? I'd be curious to have a look at the specific sheet to see if I can see anything. It doesn't make a lot of sense that it would work for PDF but not for printing.
    Ken

    I have attached the spreadsheet but I had to delete alot of it due to the file upload limitation. This basically shows 1 sheet and the print process. I hope you can make sense of it.

    Regards
    Attached Files Attached Files

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,208
    Articles
    57
    Blog Entries
    14
    I made two changes to this before I tried it:
    1) Added "Option Explicit" (no quotes) as the first line. (Forces you to declare your variables, which is a programming best practice.)
    2) Inserted the line "Dim response As Variant" as the first line of your "Sub Clear_Contents" procedure, as that variable was not declared

    Neither of these changes should have affected the way this marcro runs.

    I then ran your PRINT_GENERIC routine here. It popped up a message saying that it was printing 1 of 40, but only 1 page atually printed. Are you actually getting the full 40 phyiscally printing?

    (I ran this in Office 2010, but I can't see that making any difference.)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Quote Originally Posted by Ken Puls View Post
    I made two changes to this before I tried it:
    1) Added "Option Explicit" (no quotes) as the first line. (Forces you to declare your variables, which is a programming best practice.)
    2) Inserted the line "Dim response As Variant" as the first line of your "Sub Clear_Contents" procedure, as that variable was not declared

    Neither of these changes should have affected the way this marcro runs.

    I then ran your PRINT_GENERIC routine here. It popped up a message saying that it was printing 1 of 40, but only 1 page atually printed. Are you actually getting the full 40 phyiscally printing?

    (I ran this in Office 2010, but I can't see that making any difference.)

    Ken

    I have just tested what you said and you are indeed correct, even though it says that its printing 40/80 or even 200 pages it will still only print the variable of Z1. This is very strange indeed but in any case does work correctly.

    Would you be able to help with 1 last problem.

    You will see that when entering the data onto the 40 pages that there is a break in the data due to page headings. I am copying this data entered to another worksheet an of course there is spaces within the datasheet that need to be deleted.

    This that I did is not working.

    Sub RemoveEmptyRows()
    On Error Resume Next
    Range("II886:JG927").Select
    Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End Sub
    Can you suggest a way of deleting all empt cells within any size range of cells.

    Many thanks for your help

    Regards

    Antony

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,208
    Articles
    57
    Blog Entries
    14
    I can, Antony.

    What I do when I get a non-contiguous range of data (a table with blank rows), is this:
    • I figure out what my last column of data is
    • I run a formula down the next column to the very last row in the data table, which adds one to the number in the cell above
    • I copy that, then pastespecial->values
    • I then sort the table by the first column
    • Use Autofilter to filter out the records you want to delete
    • Delete the visible cells
    • Turn off the autofilter
    • Resort the table by that last column we inserted (to put it back in the original order)
    • Delete the last column


    Now, that might sound like a lot, but you can pretty much record the entire thing to get the syntax, then tweak it to be a bit more dynamic.

    I'm not going to be able to get back to this for about 24 hours, but if you can start working on that part, I can hlep you tweak it or work through it later. (I'd like you to give it a go to begin with and post the code though, so I can see where the data will end up and how big it will be.)

    Try these manual navigation steps before you start to record the macro:
    • End + Right Arrow will move you to the last column of data in your table
    • Arrow over to the right again, and End + Down Arrow will move you to the last row in the worksheet
    • Left arrow will move you back to the last colum
    • End + Up Arrow will move you to the last row of your data
    • Right Arrow will take you to the first blank column
    • Shift + Up Arrow will select the blank column from the last row to the first row
    • Still holding down Shift, press Down Arrow once to shorten that range
    • Enter ={Up Arrow}+1 and press CTRL+Enter (that will enter that formula in all selected cells)
    • Press CTRL+C to copy
    • Press CTRL+ALT+V -> V to paste values
    • Now you're ready to sort and apply the autofilter


    Hopefully that makes some sense.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  9. #9
    Quote Originally Posted by Ken Puls View Post
    I can, Antony.

    What I do when I get a non-contiguous range of data (a table with blank rows), is this:
    • I figure out what my last column of data is
    • I run a formula down the next column to the very last row in the data table, which adds one to the number in the cell above
    • I copy that, then pastespecial->values
    • I then sort the table by the first column
    • Use Autofilter to filter out the records you want to delete
    • Delete the visible cells
    • Turn off the autofilter
    • Resort the table by that last column we inserted (to put it back in the original order)
    • Delete the last column


    Now, that might sound like a lot, but you can pretty much record the entire thing to get the syntax, then tweak it to be a bit more dynamic.

    I'm not going to be able to get back to this for about 24 hours, but if you can start working on that part, I can hlep you tweak it or work through it later. (I'd like you to give it a go to begin with and post the code though, so I can see where the data will end up and how big it will be.)

    Try these manual navigation steps before you start to record the macro:
    • End + Right Arrow will move you to the last column of data in your table
    • Arrow over to the right again, and End + Down Arrow will move you to the last row in the worksheet
    • Left arrow will move you back to the last colum
    • End + Up Arrow will move you to the last row of your data
    • Right Arrow will take you to the first blank column
    • Shift + Up Arrow will select the blank column from the last row to the first row
    • Still holding down Shift, press Down Arrow once to shorten that range
    • Enter ={Up Arrow}+1 and press CTRL+Enter (that will enter that formula in all selected cells)
    • Press CTRL+C to copy
    • Press CTRL+ALT+V -> V to paste values
    • Now you're ready to sort and apply the autofilter


    Hopefully that makes some sense.
    Ken I have only been using excel for the last week so alot of this is very new to me. I have attempted to do what you said but it did not work out for me and I have to admit I did not entirely understand line 2 and 3.

    I have attached what I have done.

    Looking at the whole program I have done now I should not have done 40 pages with 40 headers and there probably is someway of having 600 lines of data with 1 header and putting that header of each of the 40 pages when it prints out.

    I am learning as fast as I can but after a couple of hours the frustration is setting in.

    I have sorted the data but the rows are not deleting.

    I have attached the file that I was using to test this.

    Regards
    Attached Files Attached Files

  10. #10
    Quote Originally Posted by antonywalsh View Post
    Ken I have only been using excel for the last week so alot of this is very new to me. I have attempted to do what you said but it did not work out for me and I have to admit I did not entirely understand line 2 and 3.

    I have attached what I have done.

    Looking at the whole program I have done now I should not have done 40 pages with 40 headers and there probably is someway of having 600 lines of data with 1 header and putting that header of each of the 40 pages when it prints out.

    I am learning as fast as I can but after a couple of hours the frustration is setting in.

    I have sorted the data but the rows are not deleting.

    I have attached the file that I was using to test this.

    Regards
    Ken

    I think I have achieved what I needed, I don't think it was 100% the correct way but it did get the result. Would you be able to tell me how to copy a range of cells to the clipboard? I have now deleted the cells and I want to copy the range that has data to the clipboard to paste into another spreadsheet.

    Many thanks

    Antony

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •