BLANK PAGES-I don't want to print em-macro needed?

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: BLANK PAGES-I don't want to print em-macro needed?

  1. #1
    New Member
    Join Date
    Apr 2004
    Location
    RALEIGH, NC
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default BLANK PAGES-I don't want to print em-macro needed?

     
    SCENERIO:
    I've created a one worksheet document that has 5 pages, one below the next (I used page breaks).
    At the bottom of each page is a cell that holds an "if/and" argument designed to display a blank ("") in that cell if there is no other data on that particular page (the entire page will be blank).
    The worksheet also contains a macro that turns the "fixed decimal" option on/off when the sheet opens/closes, so decimals are automatically entered (258 displays 2.58).
    Everything works like a champ, but...

    PROBLEM:
    When I click the printer icon, the printer prints all 5 pages, even the ones left blank.
    The users won't be able to use any kind of print set up...they'll just click the "print" icon.

    I WANT:
    I want the printer to ignore the blank pages.
    I don't want to do anything but click the "print" icon.
    Any macro has to turn itself on and off when the sheet opens and closes so it won't affect other excel documents.

    An answer will bring together this little project that I designed to shave about 20 minutes of grunt work off of my coworker's day.
    Thanks in advance for any help.
    Digby67

  2. #2
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,853
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi - Welcome to the board

    In the before_print event use the printout option to printout the specific pages.

    You can use an if statement to determine what pages to print and then just use printout.

  3. #3
    New Member
    Join Date
    Apr 2004
    Location
    RALEIGH, NC
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: BLANK PAGES-I don't want to print em-macro needed?

    DrJ-thanks for the reply.
    What I really want to do is set up the sheet so all anyone will have to do is just click "print" without having to select the pages, if that's possible.
    My end users might have limited experience with excel, so I'd like to make it as simple as I can.
    Digby

  4. #4
    New Member
    Join Date
    Apr 2004
    Location
    RALEIGH, NC
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: BLANK PAGES-I don't want to print em-macro needed?

    DrJ.
    I might have misunderstood you.
    I'm obviously not very experienced at this either.
    Are you talking about selecting the pages to print after you've selected print from the file...print...options...page?
    Or are you talking about something that I'm writing like a macro?
    Can you help me out a little more if you're talking about writing something into the worksheet?
    Thanks.
    digby

  5. #5
    Board Regular
    Join Date
    Jan 2004
    Location
    Melbourne
    Posts
    3,459
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think you want a macro that will change the print area to be printed. Can you advise where the cells are that show ""?

    If so, we can update the macro so that it checks each one until it finds the first blank one and then sets the print area accordingly.
    There are three kinds of people - those that can count and those that can't.

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: BLANK PAGES-I don't want to print em-macro needed?

    Hi DIGBY67:

    In line with the suggestion by tactps regarding setting Print Area, to print contiguously, you will need to hide the rows of the page that is blank -- and that can be done using a macro.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  7. #7
    Board Regular
    Join Date
    Jan 2004
    Location
    Melbourne
    Posts
    3,459
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    To explain further, say the cell with the formula is every 50 rows in column E, and you want to print columns A to G:

    Sub Macro4()
    Dim counter As Integer
    Range("E50").Select
    counter = 100
    PrintRow = ActiveCell.Row
    Do Until ActiveCell.Text = ""
    Range("E" & counter).Select
    PrintRow = ActiveCell.Row - 50
    counter = counter + 50
    Loop
    ActiveSheet.PageSetup.PrintArea = "$A$1:$G$" & PrintRow
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    End Sub
    There are three kinds of people - those that can count and those that can't.

  8. #8
    New Member
    Join Date
    Apr 2004
    Location
    RALEIGH, NC
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: BLANK PAGES-I don't want to print em-macro needed?

    Each page is 2 columns by 33 rows.
    Each page is really a bank deposit ticket with a potential total on every 33rd line of column B.
    If the first row of a page has an entry (ticket started) but the first row of the next page is blank (don't need the next ticket) then the last cell on the used page (deposit ticket) prints a total. You're done.
    However, if the first line on the following page has an entry (you needed another deposit slip to continue listing your checks) then the last cell of the deposit ticket you just filled up displays "see next" on the last line.
    Finally, if the first line on a particular page (ticket) is blank (you didn't need that deposit slip) then the last line on that slip displays blank ("").
    SO...
    I can't really let the print area end on the first blank row it gets to, because there will probably be blank rows between the end of the entries and the total at the bottom on the last deposit ticket she needed.
    I'd need something that would say "only print everything above row 33 if 34 is blank...if 34 isn't blank, then print everything above row 63 if 64 is blank...if 64 isn't blank, then...etc."
    I hope this helps clarify.
    Any hope?
    digby

  9. #9
    Board Regular
    Join Date
    Jan 2004
    Location
    Melbourne
    Posts
    3,459
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: BLANK PAGES-I don't want to print em-macro needed?

    To adjust my code (if I read it correctly):

    Code:
    Sub Macro4()
    Dim counter As Integer
         Range("B33").Select
         counter = 66
         PrintRow = ActiveCell.Row
         Do Until ActiveCell.Offset(1, 0).Text = ""
         Range("B" & counter).Select
         PrintRow = ActiveCell.Row - 33
         counter = counter + 33
         Loop
         ActiveSheet.PageSetup.PrintArea = "$A$1:$G$" & PrintRow
         ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    End Sub
    If this doesn't work, then you should post some samples and it will probably make more sense to me.

    I am assuming that once it hits a blank, then it should stop (you don't have a slip completed, then a blank, then another completed).
    There are three kinds of people - those that can count and those that can't.

  10. #10
    Board Regular Barry Katcher's Avatar
    Join Date
    Feb 2002
    Location
    Dog Beach, Florida. Yeaahh!
    Posts
    4,053
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: BLANK PAGES-I don't want to print em-macro needed?

      
    Use a formula similar to that in column A of the sample below. Then, record a macro to AutoFilter on column A, using "show" as the filter. Place the code in the sheet module, in a Before_Print macro. You'll also have to record a macro to "Show all" and place it in an After_print macro.

    To avoid using two macros, record as above to filter, then to print the range, then to unfilter, and place it in a standard module. Then assign it to a giant button on top of the sheet, with "PRINT" in 48 point bold font, so the dummy end-users can perceive that this is how one prints this sheet. Works for me and all my dummies.

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: xl2000 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    printSlip 1xxxx50
    2
    printyyyyyyyyyyyy
    3
    printzzzzzzzzzzzz
    4
    printSlip 2xxxx100
    5
    printyyyyyyyyyyyy
    6
    printzzzzzzzzzzzz
    7
    hide
    8
    hide
    9
    hide
    10
    150
    Sheet2

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
    Barry-

    Photo Restoration/Enhancement

    http://www.smiledogproductions.com
    click below for detour


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com