Dynamic Print Range in Excel 2010

Question

I am trying to set up a dynamic print range to print a sheet in a work book that is populated from another sheet in the same workbook. I seem to be having trouble. I set up a named range in Name Manager called lateral as follows:

=OFFSET('Slide Sheet Print Lateral'!$A$27, 0, 0, COUNTA('Slide Sheet Print Lateral'!$A:$A), COUNTA('Slide Sheet Print Lateral'!$1:$1))

I am stuck trying to write the VBA code ( I know nothing about VBA) I have this...

Sub Printarea()
    ActiveSheet.PageSetup.Printarea = "lateral"
End Sub

I get an error "Run Time Error '1004'"

Can anyone help?

Obviously I didn't use your spreadsheet but I did a test locally based on your code and it seemed to work without any errors. — Neil
make sure that lateral is defined on a workbook level, rather than on a sheet level, else you may have to qualify the name using Slide Sheet Print Lateral!lateral. see this MSDN for more details — SeanC

Alan K Alan K · Accepted Answer · 2012-11-26T10:38:03

The last two arguments specify the height and width of the range "lateral". They count the number of non-empty cells. Like Neil I found no problem with your code provided that:

You are on the Slide Sheet Print Lateral sheet (otherwise the reference to Activesheet will barf because you're trying to set the print range for the active sheet to a range on a different sheet); AND
There is something in column A and row 1 of the Slide Sheet Print Lateral sheet. However if there isn't, you'll be specifying a height and/or width for the range of zero. This is an invalid range reference and then you'll get the 1004 error.

The only way you can safely avoid that is to get the CountA values in your VBA code before you assign the range; if either is zero, warn the user and abort.

I'd also recommend that you not use method or property names for procedures like that; you can often get away with it but sometimes it can cause problems. Call the procedure something like SetMyPrintRange to be safe.

Edit: Upon reflection I wouldn't bother messing around checking the count; just try to get a reference to the range and if you can't, then tell the user what to do. Try this:

Sub SetMyPrintArea()

    Dim l As Long
    Dim wks As Excel.Worksheet
    Dim rng As Excel.Range

    'Check that the worksheet exists.
    On Error Resume Next
    Set wks = ThisWorkbook.Worksheets("Slide Sheet Print Lateral")
    On Error GoTo ErrorHandler

    'If it doesn't, throw an error which will send it to the error handler.
    If wks Is Nothing Then
        Err.Raise vbObjectError + 20000, , _
         "The worksheet Slide Sheet Print Lateral is not in this workbook."
    End If

    'Try to get the reference to the range. If we can't, there's something wrong.
    On Error Resume Next
    Set rng = wks.Range("lateral")
    On Error GoTo ErrorHandler

    If rng Is Nothing Then
        Err.Raise vbObjectError + 20000, , _
         "Cannot find the range named 'lateral'. Please ensure that there is " _
         & "content in row 1 and column A of the Slide Sheet Lateral sheet."
    End If

    wks.PageSetup.Printarea = "lateral"

ExitPoint:

'Just cleaning up the object references
'to leave the place tidy...
On Error Resume Next
Set rng = Nothing
Set wks = Nothing
On Error GoTo 0

Exit Sub

ErrorHandler:

'Display the message and go to the exit point.
MsgBox "Error " & Err.Number & vbCrLf & Err.Description

Resume ExitPoint

End Sub

Dynamic Print Range in Excel 2010

1 Answers