Caroline,

Try this version, below.

HTH,
Bernie
MS Excel MVP


Sub TryNow()
Dim myR As Range
Dim Derivative As Range
Dim Cash As Range
Dim myCell As Range
Dim Counter As Integer

Set Derivative = Worksheets("Portfolio Valuation") _
.Cells.Find("DERIVATIVE LIABILITIES")
Set Cash = Sheets("Cash Summary").Cells.Find("CASH")

With Worksheets("Portfolio Valuation")
Derivative.EntireRow.Insert
Set myR = .Range(Derivative(0, 1), _
.Cells(65536, Derivative.Column).End(xlUp))
End With
myR.AutoFilter Field:=1, Criteria1:="liab"

Counter = 2
For Each myCell In myR.SpecialCells(xlCellTypeVisible)
myCell.EntireRow.Copy
Cash(Counter, 1).EntireRow.Insert
Counter = Counter + 1
Next myCell

myR.EntireRow.Delete

End Sub


"CarolineHedges" <CarolineHedges.2btd0x_1154355803.2824@excelforu m-nospam.com wrote in message
news:CarolineHedges.2btd0x_1154355803.2824@excelfo rum-nospam.com...

The problem I have though is that I dont know where in the spreadsheet
the row might appear, except that I know it will be after another
particular. I have set this particular word as a variable but it wont
work:

Dim myR As Range
Set Derivative = Cells.Find("DERIVATIVE LIABILITIES")
Sheets("Portfolio Valuation").Select
Set cash = Cells.Find("CASH")
With Worksheets("Portfolio Valuation")
Set myR = .Range(.Range(cash), .Range(Derivative).End(xlUp)) End
With
myR.AutoFilter Field:=1, Criteria1:="liab"
myR.SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Worksheets("Cash Summary").Range("Derivative").End(xlUp)(2)
myR.EntireRow.Delete

So on the "portfolio valuation" the rows which contain the word "Liab"
which I want to move (to sheets "Cash Summary") will appear after the
word CASH. On the cash summary I want to move these lines to two rows
after the word "Derivative Liabilities.

It doesn't like the line in red.

Also, sorry to ask so many questions: can i adapt this so instead of
moving the lines to another sheet, I can move them to futher down the
sheet, to two cells after the word "Derivative" appears?

Thanks

Caroline

Also, I am a beginner at this stuff, but I haven't been setting
variables using Dim. What does Dim actually do/mean?


--
CarolineHedges
------------------------------------------------------------------------
CarolineHedges's Profile: http://www.excelforum.com/member.php...o&userid=35705
View this thread: http://www.excelforum.com/showthread...hreadid=566561