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

  • Thread starter Thread starter KimberlyC
  • Start date Start date
K

KimberlyC

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
 
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
 
Thanks!!
It works great..
Bob Phillips said:
Sub PrintareaDeductions2().
Activesheet.PageSetup.PrintArea = Range("A1",
BottomCornerDed(Activesheet)).Address
Call PrintActiveSheet
End Sub
Click to expand...
 
Thanks !!
I'll try this out!

STEVE BELL said:
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
Click to expand...
 
Back
Top