Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Print area by range name (Excel 97)

    In using Lotus years ago, I was able to define a print area by using its range name. Somewhere in the dialog boxes available one could type PLPG1 or PLPG2 to set the print area equal to that defined range. This was useful when the same page had more than one area needing printing fairly regularly. The range name had already defined the different areas and you didn't have to re-specify an area manually.
    I have tried for some time to figure out how to use the Insert/RangeName function to do the same thing in Excel. Is this possible? I have the set print area button on my toolbar, and there's the set print area menu item, but nowhere can I find a dialog box to enter: PLPG1 as a pre-defined area. Any ideas? TYIA

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Print area by range name (Excel 97)

    You can use range names in the Define Name dialog. See attached.

    Andrew C
    Attached Images Attached Images

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Print area by range name (Excel 97)

    Dialog Box:
    File - PageSetup - Sheet tab - "print area"

    Or Try this line in a macro:

    ActiveSheet.PageSetup.PrintArea = Range("PLPG1").Address

  4. #4
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print area by range name (Excel 97)

    OK! Thanks for two useful solutions. <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Print area by range name (Excel 97)

    I thought of something else that might be useful:
    I attached a sheet which gives an example of changing the desired print range through a combobox and selecting the desired range.
    4 ranges were created PrintAreaA - C, PrintAreaAll.
    The combobox allows selecting each of the ranges.

    hope you find it useful,
    Steve
    Attached Files Attached Files

  6. #6
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print area by range name (Excel 97)

    That's very nice. I have to look at it a bit to understand what's going on. I don't follow the area number stuff at K1:L1. Is it in the macro? and the names below it....the A, B,Cand All names. I guess the Combo Box A,B,C link to that section, which links to the defined range names below. I will be incorporating this into many report sheets after I get to know it. Thanks for your interest.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Print area by range name (Excel 97)

    I should have said more about, I guess I was getting lazy:

    K11:L21 is a Paste List of the defined names and can be deleted if desired, it was only for reference purposes.
    The ComboBox - Input range (where it lists from) is PrintRanges(K3:K6)
    The ComboBox - Cell Link (where it puts the "row Number" of the selection) is PrintAreaNum(K1)
    L3:L6 is the range names of the Print Regions that you have defined (you could have done everything with K3:K6 being the names and being used in the comboBox, but I like the ability of being more descriptive in K3:K6 and just having the names next to it)
    PrintAreaSelected (Cell L1) uses an index to get the value in L that matches the K-selected.
    The region "name" is =indirect(PrintAreaSelected) changes as the PrintAreaSelected changes

    The combobox was assigned a macro:
    Sub ChangePrintArea()

    Application.Calculate
    ActiveSheet.PageSetup.PrintArea = Range("name").Address

    End Sub

    This calculates to make sure the rangenames are updated after the pulldown, and then defines the printarea.

    I would put the items in K & L on another sheet in a real application and that sheet most likely would be hidden.

    Hope this is more clear.
    Steve

Posting Permissions

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