Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Can I control printing with a macro

    After running a simple macro that filters a worksheet, I need a PRINT macro that would select columns A-L, set "fit columns to page", and print.
    I thought I could record that myself, but it's not working. When I recorded the macro, it worked ONCE but not in general if I run another filter.

    I get this error:

    Selection.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False



    Then, my next trick is to then select columns M-Y ONLY if G3 is not equal to 0, set "fit columns to page" and print.

    Can this be done?
    Last edited by kweaver; 2015-11-10 at 10:07.

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    No Macro Suggestion

    What I would suggest is using Excel "Custom Views" This handy little item allows you to present not only the way a report is viewed on screen but also remembers all the filters and print settings.

    In the particular case I would set up 3 Custom Views

    1 Full View
    2 Summary1
    3 Summary2

    Make sure you have the workbook exactly the way you want it to be viewed and printed and then give the view its name.

    Laslty in a blank cell put an IF function that tests G3 and if Zero shows the message "Select Custom View Summary2 for this report.

    Hope this helps.
    Last edited by duthiet; 2015-11-20 at 17:04.

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    KW,

    Try these.

    In a standard module:
    Code:
    Sub FitPrint_A_L()
        Columns("A:L").Select
        ActiveSheet.PageSetup.FitToPagesWide = 1
        Selection.PrintOut
        ActiveSheet.PageSetup.PrintArea = ""
        Range("A1").Select
    End Sub
    
    Sub FitPrint_M_Y()
        If Range("G3") <> 0 Then
            Columns("M:Y").Select
            ActiveSheet.PageSetup.FitToPagesWide = 1
            Selection.PrintOut
            ActiveSheet.PageSetup.PrintArea = ""
        End If
        Range("A1").Select
    End Sub

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Perfect!

    Thanks.

Posting Permissions

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