Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003

    Arrow Unanswered: Print with a button

    I have 43 pages (tabs) and have 1 main page

    some of the 43 pages when printed do not print on 1 single page because it has more rows to fit

    I could go multiselect page and say print to fit within a page

    Can that be done directly from a button so that they ( dump people)
    dont have to multiselect and do page setup to print within a page

    By writing a macro
    I dont know how to write a macro so any instruction should be from basic

    thanks for help

  2. #2
    Join Date
    Jan 2004
    Aberdeen, Scotland
    Hi sjumma

    have you ever used the macro recorder before this will produce code for you then ou can tidy this up as required,

    this code should printout all the worksheets for you
    after setting up the pagesetup
    i havnt tested the last line but i think ot should work for you
    Sub PrintOutMacro()
    'Macro to set up print preview and then print each sheet
    'written by David Coutts DB Forums (
    Dim wrk As Worksheet
    'apply page settings identical to each page
    For Each wrk In Worksheets
        With wrk.PageSetup
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = 1
        End With
    Next wrk
        'havnt tested this line but the rest works
    End Sub
    now all you need to do is create a button and attach the macro to that,
    there are 2 methods i know of doing this

    1. Use the Control toolbox and add a button,
    from the corresponding click event call the sub above i.e.
    call PrintOutMacro

    2. use the forms toolbox and add a button
    right click on the created button and assign your macro to it

    i tend to prefer to do option 1


  3. #3
    Join Date
    Oct 2003

    Arrow thanks

    It works very nice

    After printing it keeps all tabs (44) select does not return to normal unless i click on it

  4. #4
    Join Date
    Oct 2003
    Just a note. The Control Toolbox approach works well, but if you have any need for older Excel or Mac Excel, then ActiveX controls are not supported.

    To get around that, use the Forms Toolbar, and choose a button and assign the macro to it. Functions the same way.
    old, slow, and confused
    but at least I'm inconsistent!

    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Oct 2003

    Arrow get trickier

    My 43 pages contain 'Y' in some column and emty space ' ' in others
    and they lie between column D AND 'J' (Total 7 for seven days)
    First 3 columns contains other information ( L or I; starttime; stoptime)

    But the number of rows differ
    can i count 'Y' on the sheet that has first column value as I (shot for international)
    ie column D AND j

    AND Till the last row that contains information

    Mind you after that all will be empty ' ' and i dont want to count empyt space in those
    Last edited by sjumma; 01-27-05 at 14:33.

  6. #6
    Join Date
    Jan 2004
    Aberdeen, Scotland
    by Individual lline you can use

    or for the whole sheet use

    change the ranges to suit your needs
    Last edited by DavidCoutts; 01-28-05 at 08:31. Reason: additional info

Posting Permissions

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