Print Named Ranges

  • Hi all -

    I am working on building a better report manager using named ranges

    1.) Loop through the workbook and find all named ranges
    Output the named ranges to a worksheet

    2.) Read the values from the worksheet and print each one

    Seem simple enough and is really just first step
    Would also like to store all print settings next to each named range to be modified by user as needed and used to format report before printing each range as needed

    But I'm getting ahead of myself.
    First is to print a named range

    My code is below
    Error msg:


    Run Time Error 91
    Object variable or With Block Variable Not Set

    Debug points here:

    Set rngPrint.Name = wsPrnt.Range("B" & lngRows).Value

    Though I'm not really sure as to why

    Thanks much

    Full code:

    1. Loop through all named ranges and output to worksheet. This part works fine:

    Snippet below is where I am trying to print the named range

  • Re: Print Named Ranges

    Hi Dave

    Thanks for your help.
    unfortunately, I'm not sure where you are going.

    I inserted a new sheet and pasted the name into cell A1
    It returned a #Value! error

    I see that I used this snippet in some other code:

    With Sheets("Market") 
            .PageSetup.PrintArea = rngPrint.Address 
        End With

    A little like what I am trying to do now, except the activesheet at the moment is named "prnt" The named range exists on some other sheet in the workbook. Only the text name is stored on the sheet "prnt"

    Once I have read the name of the range to be printed, is it possible to make the sheet with the named range active and then assign the named range to the print range?

    Thanks much

  • Re: Print Named Ranges

    Hi PCI,

    Many thanks for your help.
    It is a generic vba solution that I am trying to create so that I can port a homemade printing manager to many different workbooks. I find the downloadable Report Manager very lacking.

    At any rate, I downloaded the file and took a look
    Appears to only get the names of named ranges in the workbook and list them.

    My code already does that (many more lines than yours). But it works.

    Now what I would like to do in the next procedure is read the name of the range that was listed
    And then print that range. In the end, I will loop through all of the named ranges and print each one.

    Thanks again for your help.
    marc[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Hi all -

    I've done quite a bit of searching on the internet and tried many things - all of which fail.

    The code below works up to

    Debug.Print strName

    Which returns the string tblWeightDist as expected (tblWeightDist is a named range - name is stored in a cell in the workbook)

    The code below error's at

    Set rngPrint.Name = strName

    So apparently Excel does not like me trying to assign a string variable to a

    I guess where I am struggling is

    • how to now pass strName into a named range
    • get the address of the named range
    • printout the address

    Full code below

    Thanks much for the great help

  • Re: Print Named Ranges

  • Re: Print Named Ranges

    Hi Dave,

    Thanks for that - very useful and I get a better understanding of working with printing named ranges

    I'm still stuck on the idea of reading the values from a worksheet however.
    I envision a table that the user can setup and modify as needed for all page setup settings as Roy posted here for a multi-line header read from a worksheet.

    I'm thinking that I can read the list of named ranges into a combo box, then based on my or other user's selection of a single page to print from the workbook all page setup settings would update based on the index value of the combo box and the corresponding values for page setting stored to the right of each named range

    A B C D
    ===== ========= ======== =======
    named Header Header Header
    range Line 1 Line 2 Line 3
    ====== ========== ======== =======
    tblWeights Looney Tunes, Inc. Bugs Bunny Sub., Inc Consolidated Rollup

    This table should continue out to exhaust all possible page setup options
    Appears to be 25 other page setup options that can be stored on a worksheet and values read when a user chooses an item from a combo box. This would make a very nice customizable print manager to be sure

    A small workbook with the table setup is attached

    Thanks again for your help

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!