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


---
Regards,
Norman


"BrianW" wrote in message
...
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