unable to set print range with named range's address -- need a second pair of eyes

[OaklandJim]

Team Messieurs Excel

I know there is something simple that I'm missing so please have a quick look. Sub is merely trying to set the print area (and row and column headers) for printing. It hangs on my attempt to set the Print_Area range (PrintRange property) to the address of a named range. The NAME of the range (whose address) is used to set the worksheet Print_Area is specified by param psPrintArea. Here is what I have.

Private Sub PortfolioPrintAreaSetup( _
    psPrintArea, _
    psRowRange, _
    psColRange, _
    Optional pwsSheet As Worksheet)

    If IsMissing(pwsSheet) _
     Then
        pwsSheet = ActiveSheet
    End If

    Application.PrintCommunication = False

Debug.Print pwsSheet.Range(psPrintArea).Address
'Result: $A$3:$BK$112 -- the correct address

Debug.Print pwsSheet.Name
'Result: Portfolio -- the correct worksheet name

    With pwsSheet.PageSetup
        .PrintArea = .Range(psPrintArea).Address
        .PrintTitleRows = .Range(psRowRange).Address
        .PrintTitleColumns = .Range(psColRange).Address
    End With

    Application.PrintCommunication = True

End Sub

 

[JoeMo]

Haven't looked carefully at your code, and haven't tested this, but I'd try replacing the with-end with block just before Application.PrintCommunication = True with this:

With pwsSheet
    With .PageSetup
            .PrintArea = .Range(psPrintArea).Address
            .PrintTitleRows = .Range(psRowRange).Address
            .PrintTitleColumns = .Range(psColRange).Address
    End With
End With

 

[OaklandJim]

Thanx Joe but Nope. That is where I started. Retried it just now. It must be something obvious that I'm not seeing.

 

[JoeMo]

Thanx Joe but Nope. That is where I started. Retried it just now. It must be something obvious that I'm not seeing.

If the Print_Area is a named range, does this work?
With .pageSetup
.PrintArea = .Range("Name of named range").address
......

 

[OaklandJim]

I appreciate the effort Joe, really. Thank you.

That is what I am trying to do. I'm missing something obvious. And, I know that I've done it in the past.

I went in a tangential direction. I fiddled a bit with the "built-in" named ranges for printing and wuz able to set RefersTo for THOSE. A half-bassackwards approach that seems to work.

    With pwsSheet

        sRefersTo = "=" & .Range("Print_Area_All").Address

        .Names("Print_Area").RefersTo = sRefersTo
    
        sRefersTo = "=" & .Range(psColRange).Address _
        & "," & .Range(psRowRange).Address
        
        .Names("Print_Titles").RefersTo = sRefersTo
    
    End With

 

[JoeMo]

Glad you got it sorted - thanks for the reply.