Formula question

[Kristal_81]

I am trying to find a formula to copy the rows that contain "suzanne" in
column C and "open" in column B to another worksheet...anyone have any
ideas???

 

[Simon Lloyd]

I am trying to find a formula to copy the rows that contain "suzanne" in
column C and "open" in column B to another worksheet...anyone have any
ideas???Formulae can not copy anything, they can display a result, so if your

criteria was met and you had a whole row of formula to do it it could
display the row that meets your criteria, however, if you changed either
suzanne or open then you would lose your results, the only way to
achieve this is with VBA like this, this is standard module code:

Code:
--------------------
Sub find_and_move_suzanne()
Dim rng As Range, MyCell As Range
Set rng = Sheets("Sheet1").Range("C1:C" & Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row)
For Each MyCell In rng.SpecialCells(xlCellTypeVisible)
If LCase(MyCell.Value) = LCase("suzanne") And LCase(MyCell.Offset(0, -1).Value) = LCase("open") Then
MyCell.EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next MyCell
End Sub

--------------------


*How to add and run a Macro*1. *Copy* the macro above pressing
the keys *CTRL+C*
2. Open your workbook
3. Press the keys *ALT+F11* to open the Visual Basic Editor
4. Press the keys *ALT+I* to activate the *Insert menu*
5. *Press M* to insert a *Standard Module*
6. *Paste* the code by pressing the keys *CTRL+V*
7. Make any custom changes to the macro if needed at this time.
8. *Save the Macro* by pressing the keys *CTRL+S*
9. Press the keys *ALT+Q* to exit the Editor, and return to Excel.

*To Run the Macro...*
To run the macro from Excel, open the workbook, and press *ALT+F8* to
display the *Run Macro Dialog*. Double Click the macro's name to *Run*
it.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[Jacob Skaria]

You will have to use a macro to copy this. The below macro will copy the
mentioned rows from Sheet1 to Sheet2. Adjust the sheet names if any changes.
If you are new to macros set the Security level to low/medium in
(Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11.
From menu 'Insert' a module and paste the below code. Save. Get back to
Workbook. Run macro from Tools|Macro|Run <selected macro()>


Sub Copyrows()
Dim wb As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lngRow As Long
Dim lngLastRow1 As Long, lngLastRow2 As Long

Set wb = ActiveWorkbook
Set ws1 = wb.Sheets("Sheet1")
Set ws2 = wb.Sheets("Sheet2")

lngLastRow1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row
lngLastRow2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row

For lngRow = 1 To lngLastRow1
If StrComp(ws1.Range("C" & lngRow), "suzanne", vbTextCompare) + _
StrComp(ws1.Range("b" & lngRow), "open", vbTextCompare) = 0 Then
ws1.Rows(lngRow).Copy ws2.Rows(lngLastRow2 + 1)
lngLastRow2 = lngLastRow2 + 1
End If
Next
End Sub

If this post helps click Yes