Changing code that set's print area...to active sheet only

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:uDr#[email protected]...
> Sub PrintareaDeductions2().
> Activesheet.PageSetup.PrintArea = Range("A1",
> BottomCornerDed(Activesheet)).Address
> Call PrintActiveSheet
> End Sub
>
>
> --
> HTH
>
> Bob Phillips
>
> "KimberlyC" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
>
>