I want a macro to look in column A, find the last entry "F", then to insert a
row below this and copy all formatting and formulae from named range
"Freehold" into this row, leaving the cursor in column C where user would
start inputting data.

Could someone please help.

Paste this in a general module and it will work on the active worksheet:

Sub addafterlastF()
Dim LastRow As Long
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
For x = LastRow To 1 Step -1
Cells(x, 1).Select
b = ActiveCell.Text
If b = "F" Then
Cells(x + 1, 1).Select
Selection.Insert Shift:=xlDown
Range("Freehold").Select
Selection.Copy
Cells(x + 1, 1).Select
ActiveSheet.Paste
End If
Next
Cells(1, 3).Select
End Sub

Will that do?

Mike

"Karen McKenzie" wrote:

I want a macro to look in column A, find the last entry "F", then to insert a
row below this and copy all formatting and formulae from named range
"Freehold" into this row, leaving the cursor in column C where user would
start inputting data.

Could someone please help.

That works great, thanks. Only thing I need to change is that "Freehold" is
within a range of hidden rows. As we've copied the format, the rows inserted
are also hidden. How do I overcome this?

"Mike" wrote:

Paste this in a general module and it will work on the active worksheet:

Sub addafterlastF()
Dim LastRow As Long
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
For x = LastRow To 1 Step -1
Cells(x, 1).Select
b = ActiveCell.Text
If b = "F" Then
Cells(x + 1, 1).Select
Selection.Insert Shift:=xlDown
Range("Freehold").Select
Selection.Copy
Cells(x + 1, 1).Select
ActiveSheet.Paste
End If
Next
Cells(1, 3).Select
End Sub

Will that do?

Mike

"Karen McKenzie" wrote:

I want a macro to look in column A, find the last entry "F", then to insert a
row below this and copy all formatting and formulae from named range
"Freehold" into this row, leaving the cursor in column C where user would
start inputting data.

Could someone please help.