+ Reply to Thread
Results 1 to 8 of 8

Printing a named area - why won't it work as expected

  1. #1
    Registered User
    Join Date
    11-14-2012
    Location
    Sydney Australia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Question Printing a named area - why won't it work as expected

    The macro below works but … Excel does not seem to respect the print area and I find it necessary to specify From 8 to 8 in the printout statement. This is regardless of whether I use a scope of ‘Output’ or ’Workbook’

    Defined names follow :-

    Name Value Refers To Scope
    Print_Area =Output$!$K$1:$T$40 Output
    Single_Invoice =Output$!$K$1:$T$40 Workbook

    If I use From 1 to 1, it prints 1st page of 1st sheet only, rather than the named area on the sheet ‘Output’

    Any ideas would be most appreciated.

    Sub PDF_Prt()
    'Print a single Invoice to PDF
    Worksheets("Output").PageSetup.PrintArea = "Single_Invoice"
    Worksheets.PrintOut _
    From:=8, _
    To:=8, _
    Copies:=1, _
    Preview:=False, _
    ActivePrinter:="CutePDF Writer", _
    PrintToFile:=False, _
    Collate:=True, _
    PrToFileName:="", _
    IgnorePrintAreas:=False

    End Sub

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,299

    Re: Printing a named area - why won't it work as expected

    Maybe try (untested):

    Please Login or Register  to view this content.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    11-14-2012
    Location
    Sydney Australia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Printing a named area - why won't it work as expected

    Thank you Trevor for trying ...but alas same unfortunate result.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,299

    Re: Printing a named area - why won't it work as expected

    Maybe share a sample workbook with some non-sensitive data.

  5. #5
    Registered User
    Join Date
    11-14-2012
    Location
    Sydney Australia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Printing a named area - why won't it work as expected

    TMS - thx - I have attached a de-natured version of the ssheet.
    The operative sheet is 'Selection', and clicking on the pdf icon fires off the macro PDF_Prt. Whilst the macro is set to print from page 8 to 8 .. it sort of works ...but if I change anything on (say) the 1st sheet, that causes the output to add an extra page, the macro won't give the desired result.
    Of course if Excel would 'respect' my named range to 'Output!:$K$1:$T$40 ... it should work regardless of contents of earlier sheets and all would be sweet.
    Attached Files Attached Files

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,299

    Re: Printing a named area - why won't it work as expected

    OK, try this:

    Please Login or Register  to view this content.

    Regards, TMS

  7. #7
    Registered User
    Join Date
    11-14-2012
    Location
    Sydney Australia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Thumbs up Re: Printing a named area - why won't it work as expected - Solved

    Brilliant Trevor !!! It worked wonderfully

    Now two LAST Questions
    1) Why did it work and why did my macro NOT work ? (I don't expect you to write a 5 day macro course into 1 paragraph)

    2) Can you recommend any good text books on VBA ? - I looked at 'VBA for Dummies' and it didn't answer any of my questions ...maybe I am too dumb :=) !

    Finally THANK YOU for your efforts and generosity

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,299

    Re: Printing a named area - why won't it work as expected

    1. Why did it work? When you set the Print Area, you refer to Worksheets("Output") but, when you print, you just refer to Worksheets. I think that you are printing ALL pages from ALL sheets, but the From:=8, To:=8 limits the output to page 8. So, if you add any rows anywhere it will affect the page numbering and consequently what you get. That's what I think, anyway, and it seems to be borne out experimentally.

    2. Lots of good books but I tend to stand by anything from John Walkenbach. He's covered pretty much everything in all versions of Excel and, for reference books, I've always found them very readable.

    Regards, TMS



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Class use doesn't work as expected
    By seattle911 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-21-2014, 12:22 PM
  2. IF THEN ELSE formula does not work as expected
    By dexelguy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-26-2013, 02:33 PM
  3. Formula doesn't work as expected????
    By muish in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-23-2013, 04:30 AM
  4. Can no longer get LOOKUP to work as expected! (2007)
    By JayUSA in forum Excel General
    Replies: 6
    Last Post: 06-17-2009, 02:25 PM
  5. [SOLVED] setfocus does not work as expected
    By wgoldfarb in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-17-2005, 03:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1