To preserve your coding, two ways to get round:

Solution1:

For Each w In ActiveWorkbook.Worksheets
w.Columns("A:A").Select
Selection.Find(What:="Grand Total", After:=ActiveCell,
LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Offset(2, 0).Select

ActiveCell.FormulaR1C1 = "Total Surplus"
Next w

Solution 2:

For Each w In ActiveWorkbook.Worksheets
w.activate
Columns("A:A").Select
Selection.Find(What:="Grand Total", After:=ActiveCell,
LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Offset(2, 0).Select

ActiveCell.FormulaR1C1 = "Total Surplus"
Next w


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=500576