Hi

I'm using this code (with this group's help) to set the print area of each
worksheet in my activeworkbook with the name "Other Deductions" as part of
the worksheet's name.
It's working..but I now.. plan to run the code when the user is actually on
the worksheet. (printing the active sheet)
So.. I just need to set the print area of the active worksheet....as it's
set with the code below.. and I don't need it to loop thru the workbook
finding all the sheets with the name "Other Deductions" to set the print
area on.
I do not know how to change this code and function to do this..

Sub PrintareaDeductions2()
'Set Print area on Deductions sheets and prints it out.

Dim sh1 As Excel.Worksheet
Dim sh As Excel.Worksheet
Set sh1 = ActiveWorkbook.ActiveSheet
For Each sh In ActiveWorkbook.Worksheets
sh.Activate
If InStr(1, sh.Name, "Other Deductions", vbTextCompare) Then
sh.PageSetup.PrintArea = Range("A1", BottomCornerDed(sh)).Address
End If
Next 'sh

sh1.Activate
Set sh1 = Nothing
Set sh = Nothing
Call PrintActiveSheet
End Sub
*********************
Function BottomCornerDed(ByRef objSHeet As Worksheet) As Range
On Error GoTo NoCorner
Dim BottomRowDed As Long
Dim LastColumnDed As Long

If objSHeet.FilterMode Then objSHeet.ShowAllData

BottomRowDed = objSHeet.Cells(Rows.Count, 4).End(xlUp).Row
LastColumnDed = objSHeet.Cells.Cells(7,
Columns.Count).End(xlToLeft).Column
Set BottomCornerDed = objSHeet.Cells(BottomRowDed, LastColumnDed)

Exit Function

NoCorner:
Beep
Set BottomCornerDed = objSHeet.Cells(1, 1)
End Function

Thank you in advance for your help!!
Kimberly

Sub PrintareaDeductions2().
Activesheet.PageSetup.PrintArea = Range("A1",
BottomCornerDed(Activesheet)).Address
Call PrintActiveSheet
End Sub


--
HTH

Bob Phillips

"KimberlyC" wrote in message
...
Hi

I'm using this code (with this group's help) to set the print area of each
worksheet in my activeworkbook with the name "Other Deductions" as part of
the worksheet's name.
It's working..but I now.. plan to run the code when the user is actually
on
the worksheet. (printing the active sheet)
So.. I just need to set the print area of the active worksheet....as it's
set with the code below.. and I don't need it to loop thru the workbook
finding all the sheets with the name "Other Deductions" to set the print
area on.
I do not know how to change this code and function to do this..

Sub PrintareaDeductions2()
'Set Print area on Deductions sheets and prints it out.

Dim sh1 As Excel.Worksheet
Dim sh As Excel.Worksheet
Set sh1 = ActiveWorkbook.ActiveSheet
For Each sh In ActiveWorkbook.Worksheets
sh.Activate
If InStr(1, sh.Name, "Other Deductions", vbTextCompare) Then
sh.PageSetup.PrintArea = Range("A1", BottomCornerDed(sh)).Address
End If
Next 'sh

sh1.Activate
Set sh1 = Nothing
Set sh = Nothing
Call PrintActiveSheet
End Sub
*********************
Function BottomCornerDed(ByRef objSHeet As Worksheet) As Range
On Error GoTo NoCorner
Dim BottomRowDed As Long
Dim LastColumnDed As Long

If objSHeet.FilterMode Then objSHeet.ShowAllData

BottomRowDed = objSHeet.Cells(Rows.Count, 4).End(xlUp).Row
LastColumnDed = objSHeet.Cells.Cells(7,
Columns.Count).End(xlToLeft).Column
Set BottomCornerDed = objSHeet.Cells(BottomRowDed, LastColumnDed)

Exit Function

NoCorner:
Beep
Set BottomCornerDed = objSHeet.Cells(1, 1)
End Function

Thank you in advance for your help!!
Kimberly

In the ThisWorkbook module - place the below code.
It will fire when the user selects Print, or clicks the Print button (you
don't need to
make any buttons). Make your printing code generic to ActiveSheet.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
' put your code here or call a macro from here

Run MyPrintMacro <<< substitute the name of your macro here.
End Sub


--
steveB

Remove "AYN" from email to respond
"KimberlyC" wrote in message
...
Hi

I'm using this code (with this group's help) to set the print area of each
worksheet in my activeworkbook with the name "Other Deductions" as part of
the worksheet's name.
It's working..but I now.. plan to run the code when the user is actually
on
the worksheet. (printing the active sheet)
So.. I just need to set the print area of the active worksheet....as it's
set with the code below.. and I don't need it to loop thru the workbook
finding all the sheets with the name "Other Deductions" to set the print
area on.
I do not know how to change this code and function to do this..

Sub PrintareaDeductions2()
'Set Print area on Deductions sheets and prints it out.

Dim sh1 As Excel.Worksheet
Dim sh As Excel.Worksheet
Set sh1 = ActiveWorkbook.ActiveSheet
For Each sh In ActiveWorkbook.Worksheets
sh.Activate
If InStr(1, sh.Name, "Other Deductions", vbTextCompare) Then
sh.PageSetup.PrintArea = Range("A1", BottomCornerDed(sh)).Address
End If
Next 'sh

sh1.Activate
Set sh1 = Nothing
Set sh = Nothing
Call PrintActiveSheet
End Sub
*********************
Function BottomCornerDed(ByRef objSHeet As Worksheet) As Range
On Error GoTo NoCorner
Dim BottomRowDed As Long
Dim LastColumnDed As Long

If objSHeet.FilterMode Then objSHeet.ShowAllData

BottomRowDed = objSHeet.Cells(Rows.Count, 4).End(xlUp).Row
LastColumnDed = objSHeet.Cells.Cells(7,
Columns.Count).End(xlToLeft).Column
Set BottomCornerDed = objSHeet.Cells(BottomRowDed, LastColumnDed)

Exit Function

NoCorner:
Beep
Set BottomCornerDed = objSHeet.Cells(1, 1)
End Function

Thank you in advance for your help!!
Kimberly

Thanks!!
It works great..
"Bob Phillips" wrote in message
...
Sub PrintareaDeductions2().
Activesheet.PageSetup.PrintArea = Range("A1",
BottomCornerDed(Activesheet)).Address
Call PrintActiveSheet
End Sub


--
HTH

Bob Phillips

"KimberlyC" wrote in message
...
Hi

I'm using this code (with this group's help) to set the print area of
each
worksheet in my activeworkbook with the name "Other Deductions" as part
of
the worksheet's name.
It's working..but I now.. plan to run the code when the user is actually
on
the worksheet. (printing the active sheet)
So.. I just need to set the print area of the active worksheet....as
it's
set with the code below.. and I don't need it to loop thru the workbook
finding all the sheets with the name "Other Deductions" to set the print
area on.
I do not know how to change this code and function to do this..

Sub PrintareaDeductions2()
'Set Print area on Deductions sheets and prints it out.

Dim sh1 As Excel.Worksheet
Dim sh As Excel.Worksheet
Set sh1 = ActiveWorkbook.ActiveSheet
For Each sh In ActiveWorkbook.Worksheets
sh.Activate
If InStr(1, sh.Name, "Other Deductions", vbTextCompare) Then
sh.PageSetup.PrintArea = Range("A1", BottomCornerDed(sh)).Address
End If
Next 'sh

sh1.Activate
Set sh1 = Nothing
Set sh = Nothing
Call PrintActiveSheet
End Sub
*********************
Function BottomCornerDed(ByRef objSHeet As Worksheet) As Range
On Error GoTo NoCorner
Dim BottomRowDed As Long
Dim LastColumnDed As Long

If objSHeet.FilterMode Then objSHeet.ShowAllData

BottomRowDed = objSHeet.Cells(Rows.Count, 4).End(xlUp).Row
LastColumnDed = objSHeet.Cells.Cells(7,
Columns.Count).End(xlToLeft).Column
Set BottomCornerDed = objSHeet.Cells(BottomRowDed, LastColumnDed)

Exit Function

NoCorner:
Beep
Set BottomCornerDed = objSHeet.Cells(1, 1)
End Function

Thank you in advance for your help!!
Kimberly

Thanks !!
I'll try this out!

"STEVE BELL" wrote in message
news:UoeAe.21288$ZN6.4792@trnddc02...
In the ThisWorkbook module - place the below code.
It will fire when the user selects Print, or clicks the Print button (you
don't need to
make any buttons). Make your printing code generic to ActiveSheet.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
' put your code here or call a macro from here

Run MyPrintMacro <<< substitute the name of your macro here.
End Sub


--
steveB

Remove "AYN" from email to respond
"KimberlyC" wrote in message
...
Hi

I'm using this code (with this group's help) to set the print area of
each
worksheet in my activeworkbook with the name "Other Deductions" as part
of
the worksheet's name.
It's working..but I now.. plan to run the code when the user is actually
on
the worksheet. (printing the active sheet)
So.. I just need to set the print area of the active worksheet....as
it's
set with the code below.. and I don't need it to loop thru the workbook
finding all the sheets with the name "Other Deductions" to set the print
area on.
I do not know how to change this code and function to do this..

Sub PrintareaDeductions2()
'Set Print area on Deductions sheets and prints it out.

Dim sh1 As Excel.Worksheet
Dim sh As Excel.Worksheet
Set sh1 = ActiveWorkbook.ActiveSheet
For Each sh In ActiveWorkbook.Worksheets
sh.Activate
If InStr(1, sh.Name, "Other Deductions", vbTextCompare) Then
sh.PageSetup.PrintArea = Range("A1", BottomCornerDed(sh)).Address
End If
Next 'sh

sh1.Activate
Set sh1 = Nothing
Set sh = Nothing
Call PrintActiveSheet
End Sub
*********************
Function BottomCornerDed(ByRef objSHeet As Worksheet) As Range
On Error GoTo NoCorner
Dim BottomRowDed As Long
Dim LastColumnDed As Long

If objSHeet.FilterMode Then objSHeet.ShowAllData

BottomRowDed = objSHeet.Cells(Rows.Count, 4).End(xlUp).Row
LastColumnDed = objSHeet.Cells.Cells(7,
Columns.Count).End(xlToLeft).Column
Set BottomCornerDed = objSHeet.Cells(BottomRowDed, LastColumnDed)

Exit Function

NoCorner:
Beep
Set BottomCornerDed = objSHeet.Cells(1, 1)
End Function

Thank you in advance for your help!!
Kimberly