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:

Quote

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
-marc

Full code:

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

Option Explicit


Sub GetNamedRange()
    Dim nName As Name
    Dim wb As Workbook
    Dim wsPrnt As Worksheet
    Dim Row As Long
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
    End With
    
    Set wb = ThisWorkbook
    Set wsPrnt = wb.Worksheets("prnt")
    Row = 3
    
    
    With wb
        For Each nName In .Names
            wsPrnt.Range("B" & Row) = nName.Name
            Row = Row + 1
        Next
    End With
    
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .Calculation = xlCalculationAutomatic
    End With
    
    Set nName = Nothing
    Set wb = Nothing
    Set wsPrnt = Nothing
    
End Sub

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

Option Explicit


Sub PrintNamedRange()
    Dim wb As Workbook
    Dim wsPrnt As Worksheet
    Dim lngRows As Long
    Dim rngPrint As Range
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
    End With
    
    Set wb = ThisWorkbook
    Set wsPrnt = wb.Worksheets("prnt")
    lngRows = wsPrnt.Range("B65536").End(xlUp).Row
    Set rngPrint.Name = wsPrnt.Range("B" & lngRows).Value
    rngPrint.PrintOut
    
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .Calculation = xlCalculationAutomatic
    End With
    
    Set wb = Nothing
    Set wsPrnt = Nothing
    Set rngPrint = Nothing
    
End Sub

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
marc

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 range.name

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
Regards,
marc

Option Explicit


Sub PrintNamedRange()
    Dim wb As Workbook
    Dim wsPrnt As Worksheet
    Dim lngRows As Long
    Dim nName As Name
    Dim strName As String
    Dim rngPrint As Range
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
    End With
    
    'Initialize
        Set wb = ThisWorkbook
        Set wsPrnt = wb.Worksheets("prnt")
        lngRows = wsPrnt.Range("B65536").End(xlUp).Row
        strName = wsPrnt.Range("B" & lngRows).Value
    
    'Test
        Debug.Print strName
        
    'Select Range & Print
        Set rngPrint.Name = strName
        rngPrint.Select
        ActiveSheet.PageSetup.PrintArea = rngPrint.Address
        ActiveSheet.PrintOut
    
    'Cleanup
        With Application
            .ScreenUpdating = True
            .DisplayAlerts = True
            .Calculation = xlCalculationAutomatic
        End With
    
        Set wb = Nothing
        Set wsPrnt = Nothing
        Set rngPrint = Nothing
    
End Sub

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
Regards,
marc