Setting print area within macro

[Guest]

I have the following macro to select a specific worksheet and print based on
user input of worksheet name:

Sub PrintWS()
Dim ws As Worksheet
Dim sName$

On Error GoTo ErrorInPrintWS
sName = InputBox("Enter worksheet name", "Print")
If sName <> "" Then
Set ws = Worksheets(sName)
ws.PrintOut
End If

Exit Sub
ErrorInPrintWS:
Exit Sub
End Sub

I tried to alter it as follows to define a specific print range but was not
successful.

Sub PrintDeptArea()
Dim ws As Worksheet
Dim sName$

On Error GoTo ErrorInPrintWS
sName = InputBox("Enter worksheet name", "Print")
If sName <> "" Then
Set ws = Worksheets(sName)
ActiveSheet.PageSetup.PrintArea = "$A$9:$R$74"
ws.PrintOut
End If

Exit Sub
ErrorInPrintWS:
Exit Sub
End Sub

Can anyone help?
Thanks,
Tim

 

[Tom Ogilvy]

Sub PrintDeptArea()
Dim ws As Worksheet
Dim sName$

On Error GoTo ErrorInPrintWS
sName = InputBox("Enter worksheet name", "Print")
If sName <> "" Then
Set ws = Worksheets(sName)
ws.PageSetup.PrintArea = "'" & ws.Name & "'!$A$9:$R$74"
' ws.PrintOut
End If

Exit Sub
ErrorInPrintWS:
Exit Sub
End Sub

 

[Guest]

Tom,
Thanks! I works like a charm.