K
KimberlyC
Hi
I am using the following formula (with the helpof this group) to set the
print area of all worksheets with the name "Details" in the worksheet name
(there can be numerous sheets with Details in the name for ex:.. Details
(2), Details (3)) in my activeworkbook
This code sets the area to the last entry in row 7 and the last entry in col
A.
It works great.. however.. I need to ajust it to look at the last entry in
row 7 ...and the last entry in column H OR column I .....depending on
whichever one had the last entry.
So.. if the last entry in row 7 is in P7
and the last entry in column H is H90
and the last entry in I is I89, then the print area would need to be set to
A190.
I'm not sure if this is possible...
Sub PrintareaDetails()
'Set Print area on Details sheets
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, "Details", vbTextCompare) Then
sh.PageSetup.PrintArea = Range("A1", BottomCorner(sh)).Address
End If
Next sh
sh1.Activate
Set sh1 = Nothing
Set sh = Nothing
End Sub
*******************************
Function BottomCorner(ByRef objSheet As Worksheet) As Range
On Error GoTo NoCorner
Dim BottomRow As Long
Dim LastColumn As Long
If objSheet.FilterMode Then objSheet.ShowAllData
BottomRow = objSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastColumn = objSheet.Cells.Cells(7, Columns.Count).End(xlToLeft).Column
Set BottomCorner = objSheet.Cells(BottomRow, LastColumn)
Exit Function
NoCorner:
Beep
Set BottomCorner = objSheet.Cells(1, 1)
End Function
Thanks in advance!!!
Kimberly
I am using the following formula (with the helpof this group) to set the
print area of all worksheets with the name "Details" in the worksheet name
(there can be numerous sheets with Details in the name for ex:.. Details
(2), Details (3)) in my activeworkbook
This code sets the area to the last entry in row 7 and the last entry in col
A.
It works great.. however.. I need to ajust it to look at the last entry in
row 7 ...and the last entry in column H OR column I .....depending on
whichever one had the last entry.
So.. if the last entry in row 7 is in P7
and the last entry in column H is H90
and the last entry in I is I89, then the print area would need to be set to
A190.
I'm not sure if this is possible...
Sub PrintareaDetails()
'Set Print area on Details sheets
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, "Details", vbTextCompare) Then
sh.PageSetup.PrintArea = Range("A1", BottomCorner(sh)).Address
End If
Next sh
sh1.Activate
Set sh1 = Nothing
Set sh = Nothing
End Sub
*******************************
Function BottomCorner(ByRef objSheet As Worksheet) As Range
On Error GoTo NoCorner
Dim BottomRow As Long
Dim LastColumn As Long
If objSheet.FilterMode Then objSheet.ShowAllData
BottomRow = objSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastColumn = objSheet.Cells.Cells(7, Columns.Count).End(xlToLeft).Column
Set BottomCorner = objSheet.Cells(BottomRow, LastColumn)
Exit Function
NoCorner:
Beep
Set BottomCorner = objSheet.Cells(1, 1)
End Function
Thanks in advance!!!
Kimberly