Setting print area within macro

[TimMalone]

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

--
Regards,
Tom Ogilvy


"TimMalone" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[TimMalone]

Tom,
Thanks! I works like a charm.

"TimMalone" wrote:

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