Setting print area to text

[BrianW]

Hi all
I want to set the length of my print area by the text "Overall Total" in
column B. It's location can vary by up to 500 rows.
My print area always starts at cell A1:H?. ? being the row that contains
"Overall Total"
At the moment I have the print area set to A1:H200.
Can anyone tell me how to write the macro code to set the print area to the
row containing "Overall Total".
My sheet name is "Printout". & I'm using Excel 2003

Thanks in advance
Brian

 

[Norman Jones]

Hi Brian,

Try:

'=============>>
Public Sub Tester001()
Dim WB As Workbook
Dim SH As Worksheet
Dim LRow As Long
Const sStr As String = "Overall Total"

Set WB = Workbooks("Your Book.xls") '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE

With SH
LRow = .Cells.Find(What:=sStr, _
After:=.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Row
End With

SH.PageSetup.PrintArea = "$A$1:$H$" & LRow

End Sub
'<<=============

 

[BrianW]

Norm thanks for that your help.
Code works fine.
Had to twik the code slightly as I should have mentioned that the name of
the work book changes each time I use it.
Here's my final code in case other newbees may be interested

Public Sub Macro1()
Let FName = Worksheets("Pricing").Cells(5, 12) '<<== Reference to new
file name
Dim WB As Workbook
Dim SH As Worksheet
Dim LRow As Long
Const sStr As String = "Overall Total"

Set WB = Workbooks(FName) '<<==== CHANGE
Set SH = WB.Sheets("Printout") '<<==== CHANGE

With SH
LRow = .Cells.Find(What:=sStr, _
After:=.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Row
End With

SH.PageSetup.PrintArea = "$A$1:$G$" & LRow

End Sub


Once again thankyou for your time and your assistance. It's greatly
appreciated.
Have a great day