Does anyone have a simple macro for this?

[CarolineHedges]

For each/every row which has the text "liab" in column A after cel
A100, for example, cut the row and paste onto another worksheet.

Thank

 

[Bernie Deitrick]

Caroline,

If your liab values are on "Liab Sheet", and you want to move them to "Another Sheet"


Dim myR As Range
With Worksheets("Liab Sheet")
Range("A101").EntireRow.Insert
Set myR = .Range(.Range("A101"), .Range("A65536").End(xlUp))
End With
myR.AutoFilter Field:=1, Criteria1:="liab"
myR.SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Worksheets("Another Sheet").Range("A65536").End(xlUp)(2)
myR.EntireRow.Delete

HTH,
Bernie
MS Excel MVP

 

[CarolineHedges]

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?

 

[Bernie Deitrick]

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