Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does anyone have a simple macro for this?
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 -- CarolineHedge ----------------------------------------------------------------------- CarolineHedges's Profile: http://www.excelforum.com/member.php...fo&userid=3570 View this thread: http://www.excelforum.com/showthread.php?threadid=56656 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does anyone have a simple macro for this?
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" <CarolineHedges.2bt32k_1154342902.1997@excelforu m-nospam.com wrote in message news:CarolineHedges.2bt32k_1154342902.1997@excelfo rum-nospam.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does anyone have a simple macro for this?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does anyone have a simple macro for this?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with a simple macro | Excel Discussion (Misc queries) | |||
Very Simple Macro | Excel Discussion (Misc queries) | |||
maybe it's simple with macro for this one? | Excel Worksheet Functions | |||
Simple Macro | Excel Worksheet Functions | |||
Simple Macro | New Users to Excel |