copy rows to new page

[Guest]

Hello all,
I have a spreadsheet with value's of OPEN and CLOSED in Column C.
How can I create a macro that will move the rows (Columns A thru F) with
OPEN in column C to the OPEN tab and the rows with CLOSED to the CLOSED tab?

Any assistance is appreciated. I'm familar with VBA in Access, but not very
familar with code in Excel.

 

[Carim]

Hi david,

Make sure to test and to adjust as needed ...

Sub Macro1()

Dim i As Integer
Dim LastRow As Integer

LastRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
For i = 1 To LastRow
Range("C" & i).Select
If ActiveCell.Value = "OPEN" Then
ActiveCell.EntireRow.Copy
Sheets("OPEN").Select
Range("A1").Select
If Range("A1").Value <> "" Then
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Else
ActiveSheet.Paste
End If
Range("C" & i).Select
Sheets("DATA").Select
Range("C" & i).Select
ElseIf ActiveCell.Value = "CLOSE" Then
ActiveCell.EntireRow.Copy
Sheets("CLOSE").Select
Range("A1").Select
If Range("A1").Value <> "" Then
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Else
ActiveSheet.Paste
End If
Range("C" & i).Select
Sheets("DATA").Select
Range("C" & i).Select
End If
Application.CutCopyMode = xlCut
Next i
End Sub

HTH
Cheers
Carim