Formula question

  • Thread starter Thread starter Kristal_81
  • Start date Start date
K

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???
 
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
 
Back
Top