Setting print area with vba - last entry in a row and col...

[KimberlyC]

Hi

I posted a message yesterday regarding setting the print area of a
worksheet..
I've since decided to change up the worksheet a little....

So.. now my question is:
Is there a way to set the print area of all the worksheets in the
activeworkbook ...that begin with the name "cap i details" (as there can be
many sheets with the name cap i details ..for ex: cap i details (2), cap i
details (3)...etc...) to the last entry in COL A and the last entry in ROW 8
?

The code below (again) is what this group helped me out with .... when I
only had columns A-K to worry about printing..as this code sets the print
area to the columns A - K and the last entry in col A.

Now that I will have additional columns and will not always know how many
will be used... I want to set the print area to the last entry in ROW 8 and
the last entry in COL A.

This seems a bit easier to do than my previous post... but I still can seem
to figure it out..

Any help would be greatly appreicated..
I've been researching this all day...and still can't figure it out!!

****************
Public Sub PrintareaCAPIDetails()

Set sh1 = ActiveWorkbook.ActiveSheet
For Each Sh In ActiveWorkbook.Worksheets
If Left(LCase(Sh.Name), 13) = "cap i details" Then
Sh.Activate
ActiveWorkbook.ActiveSheet.PageSetup.PrintArea = "A1:K" & _
LastInCol(Sh.Range("A1"))
End If
Next
sh1.Activate

PrintForm.Show

End Sub
******************************************
Public Function LastInCol(rngInput As Range)
''Courtesy of http://www.j-walk.com, though a tad changed
Dim WorkRange As Range
Dim i As Integer, CellCount As Integer
Application.Volatile
Set WorkRange = rngInput.Columns(1).EntireColumn
Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
CellCount = WorkRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
LastInCol = WorkRange(i).Row
Exit Function
End If
Next i
End Function

 

[Jim Cone]

Sub PrintareaCAPIDetails()
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, "cap i details", vbTextCompare) Then
sh.PageSetup.PrintArea = Range("A1", BottomCorner(sh)).Address
End If
Next 'sh
sh1.Activate
Set sh1 = Nothing
Set sh = Nothing
'PrintForm.Show
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(8, Columns.Count).End(xlToLeft).Column
Set BottomCorner = objSheet.Cells(BottomRow, LastColumn)

Exit Function

NoCorner:
Beep
Set BottomCorner = objSheet.Cells(1, 1)
End Function
'----------------------------------

Jim Cone
San Francisco, USA

 

[William]

Hi

Sub test()
Dim ws As Worksheet
For Each ws In Worksheets
If Left(ws.Name, 13) = "cap i datails" Then ws.PageSetup.PrintArea = _
ws.Range(ws.Range("A1"), ws.Range("Iv8").End(xlToLeft)).Address
Next ws
End Sub
--

XL2003
Regards

William
(e-mail address removed)

 

[KimberlyC]

Thank you for your help!!

I tried this code out and it set the print area to the last entry in row
8....but it did not set it to the last entry in col A.

Thanks again!!

 

[KimberlyC]

Thank you so much!!
This works Great!

Sub PrintareaCAPIDetails()
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, "cap i details", vbTextCompare) Then
sh.PageSetup.PrintArea = Range("A1", BottomCorner(sh)).Address
End If
Next 'sh
sh1.Activate
Set sh1 = Nothing
Set sh = Nothing
'PrintForm.Show
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(8, Columns.Count).End(xlToLeft).Column
Set BottomCorner = objSheet.Cells(BottomRow, LastColumn)

Exit Function

NoCorner:
Beep
Set BottomCorner = objSheet.Cells(1, 1)
End Function
'----------------------------------

Jim Cone
San Francisco, USA

 

[William]

Hi Kimberley

I misinterpreted you question.

This assumes you want to set the print area by reference to the last cell in
row 8 and the last cell in column A.

Sub test()
Dim ws As Worksheet
For Each ws In Worksheets
If Left(ws.Name, 13) = "cap i details" And
Application.CountA(ws.Range("A:A")) > 0 Then _
ws.PageSetup.PrintArea = ws.Range(ws.Range("A1"), _
ws.Range("A" & ws.Rows.Count).End(xlUp).Offset(0,
ws.Range("Iv8").End(xlToLeft).Column - 1)).Address
Next ws
End Sub

--

XL2003
Regards

William
(e-mail address removed)