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