Does anyone have a simple macro for this?

[CarolineHedges]

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

Thanks

[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" <[email protected]> wrote in message
news:[email protected]...
>
> For each/every row which has the text "liab" in column A after cell
> A100, for example, cut the row and paste onto another worksheet.
>
> Thanks
>
>
> --
> CarolineHedges
> ------------------------------------------------------------------------
> CarolineHedges's Profile: http://www.excelforum.com/member.php...o&userid=35705
> View this thread: http://www.excelforum.com/showthread...hreadid=566561
>

[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


"CarolineHedges" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>