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

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" wrote in message
...
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,
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