Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Excel - clearing the print area in a workbook called up from another workbook

    Perhaps this can be easily done and perhaps not. So I turn to you good folk to give me a hand.

    Two workbooks are opened simultaneously.

    "Label - fill in and print.xls" is open and the statement below is in a label printing macro in that workbook.


    Windows("Layout Sheet.xls").Activate
    ActiveSheet.PageSetup.PrintArea = ""
    Windows("Label - fill in and print.xls").Activate


    The idea is for the 2nd line to clear the print area in "Layout Sheet.xls".


    The problem is that the print area in "Layout Sheet.xls" does not clear when this runs. "Layout Sheet.xls" activates and then "Label - fill in and print".xls reactivates but the print area in "Layout Sheet" has not changed.

    I want to have this happen because after my Dymo printer does the printing in "Label - fill and and print.xls" I get label print layout on screen in "Layout Sheet.xls".

    I'm just trying to automatically clear that label print area.

    Thanks,
    BH

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    bh,

    Is the correct sheet active when the Layout Sheet.xls becomes activated. You may be clearing the print area on the wrong worksheet if the worksheet that you want to clear the print area is not the active sheet.

    HTH,
    Maud

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Maudibe View Post
    bh,

    Is the correct sheet active when the Layout Sheet.xls becomes activated. You may be clearing the print area on the wrong worksheet if the worksheet that you want to clear the print area is not the active sheet.

    HTH,
    Maud
    Yes......at least that's what I intended.

    The first line of my code activates the worksheet (Layout sheet).
    The 2nd line of my code is supposed to clear the print area (in Layout sheet).

    But it doesn't clear the print area.

    Thanks,

    BH

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    bh,

    The first line of your code activates the Layout Sheet.xls workbook. When it becomes active, what sheet is showing in that workbook?

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Maudibe View Post
    bh,

    The first line of your code activates the Layout Sheet.xls workbook. When it becomes active, what sheet is showing in that workbook?

    The only worksheet in that workbook is the Layout Sheet and it does indeed show on screen.

    As an experiment I added an additional line after Line #2. It was to select Cell A1 and that worked fine. So I can create activity in the Layout Sheet.........I'm just not getting it to clear the print range.

    Thanks,
    BH

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If it's the only sheet, try replacing those three lines with this one:
    Code:
    Workbooks("Layout Sheet.xls").Worksheets(1).PageSetup.PrintArea = ""
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by rory View Post
    If it's the only sheet, try replacing those three lines with this one:
    Code:
    Workbooks("Layout Sheet.xls").Worksheets(1).PageSetup.PrintArea = ""
    Rory,

    That worked. Thank you.

    However it didn't work at first. Below is my new code with my original 3 line attempt which now also works. The problem was I had this clear print area step BEFORE the line Application.ActivePrinter = "Brother MFC-9460CDN Printer on Ne06:". Never occurred to me to try it after that change back to the Brother printer until your one line solution didn't work.

    Would have helped sort this quicker if I had originally pasted in the entire macro instead of just my 3 lines. Sorry for being dense about that.

    As always thank you everyone for the help.
    BH

    Code:
    Sub Print_straights_label()
    '
    ' Print_straights_label Macro
    ' Macro recorded 12/4/2012 by B.H. Davis
    '
    
    '
        Range("S15:X19").Select
        ActiveSheet.PageSetup.PrintArea = "$S$15:$X$19"
        Application.ActivePrinter = "DYMO LabelWriter Twin Turbo on Ne05:"
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
            "DYMO LabelWriter Twin Turbo on Ne05:", Collate:=True
    
         
        Application.ActivePrinter = "Brother MFC-9460CDN Printer on Ne06:"
        
        Windows("Layout Sheet.xls").Activate
        ActiveSheet.PageSetup.PrintArea = ""
        Windows("Label - fill in and print.xls").Activate
        
    End Sub
    Last edited by RetiredGeek; 2016-06-08 at 10:12. Reason: Added Code Tags

Posting Permissions

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