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

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

bh,

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

Reply | Quote

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

Reply | Quote

If it’s the only sheet, try replacing those three lines with this one:

Code:

Workbooks("Layout Sheet.xls").Worksheets(1).PageSetup.PrintArea = ""

Reply | Quote

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

Reply | Quote