its ok, what kept happeneing is it was deleting all cells that werent yellow
so was going on for ever! made the following adjustment and it works:

Sub movit()
Set s1 = Sheets("Sheet")
Set s2 = Sheets("Sheet2")
s1.Range("A:B").Copy s2.Range("A1")
s2.Activate

n = Cells(Rows.Count, "A").End(xlUp).Row
For i = n To 1 Step -1
If Cells(i, "A").Interior.ColorIndex = 6 Then
ElseIf Cells(i, "A").Value = " " Then

s = "A" & i & ":B" & i
Range(s).Delete Shift:=xlUp
End If
Next
End Sub

THankyou again!!

"Patrick Bateman" wrote:

AWESOME!!

cheers thats a great idea! only problem is it seems to keep repeating for
ages?

"Gary''s Student" wrote:

For example:

Sub movit()
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
s1.Range("A:B").Copy s2.Range("A1")
s2.Activate

n = Cells(Rows.Count, "A").End(xlUp).Row
For i = n To 1 Step -1
If Cells(i, "A").Interior.ColorIndex = 6 Then
Else
s = "A" & i & ":B" & i
Range(s).Delete Shift:=xlUp
End If
Next
End Sub

First all the data is copied and then the non-yellow data is reomved.
--
Gary''s Student - gsnu200758


"Patrick Bateman" wrote:

hi,

is seem to be going round in circles with this one and wandrered if you can
help?

i have two columns of data say A and B and some of the cells in column a are
coloured yellow
i am trying to write a macro that makes 2 new columns of data on a new sheet
for all the rows that have a cell in column a coloured yellow

any help or ideas would be much appreciated

thankyou

patrick