Hi Nick,

Try:

'=============
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Const sStr As String = "last risk above"

Set WB = ActiveWorkbook '<<==== CHANGE
Set SH = WB.Sheets("Sheet3") '<<==== CHANGE

Set rng = SH.Columns("B:B").Find(What:=sStr, _
After:=Range("B1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
With rng
.EntireRow.Insert
.Offset(-2).Copy
.Offset(-1).PasteSpecial Paste:=xlFormulas, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Application.CutCopyMode = False
End With
End If
End Sub
'<<=============


---
Regards,
Norman


"Nick Smith" wrote in message
...
Going round in circles here.... can anyone help please?

I need a macro to do this:

Search down column B for some specific text and then insert a new row
immediately above the found cell. The new row must copy the formulas and
formatting (but not the values) of the row above the found cell.

So, find text "last risk above" in column B - let's say this is cell B40.
Macro then copies the formatting and formulas of B39 to a new row between
B39 and B40.

Thanks in advance,

Nick