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