formula to copy cells to new worksheet

[skeez14]

hello,

I have a situation where i need to copy all line items with the word "approved" in a certain column to a new worksheet so that only approved items will be at the new sheet. can you please advise on how to get this done?

thanks in advance

[jraj1106]

Hi..
Can u give sample data?

[skeez14]

Hi, in the two columns below, pretend that this is the master list, and i need every line item where column B is "approved" to copy itself to a new worksheet, so the new worksheet will only have the approved line items. hope this explains the situation.

thanks

black approved
white denied
blue approved
yellow denied
brown approved
red approved

[jraj1106]

Hi...
Try this code. Please note u may have to change the worksheet name.

Sub COPYINTOANOTHERSHEET()
Dim rcnt As Long, cnt As Long, i As Long

Sheets("Sheet9").Select
rcnt = Sheets("Sheet9").Range("A" & Rows.Count).End(xlUp).Row
cnt = Sheets("Sheet10").Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To rcnt
If Sheets("Sheet9").Range("B" & i).Value = "approved" Then
Rows(i & ":" & i).Copy (Sheets("Sheet10").Range("A" & cnt))
cnt = cnt + 1
End If
Next
End Sub

Regards...